DISTINCT rows in a table based in conditions

MarkKs

Hello! I'm creating a chart based on data stored in a table with tables 5772 and 5773 as source of these data. I want Picks and Put-Away lines based on his header (per User). So I created a code to do it but it's not efficient and decreases NAV's performance.


// Works only for Put-Away - approximately 341 lines in the table
l_RecWhseActHdr.RESET;
l_RecWhseActHdr.SETRANGE(Type,l_RecWhseActHdr.Type::"Put-away");
l_RecWhseActHdr.SETFILTER("Assigned User ID",'<>%1','');
IF l_RecWhseActHdr.FINDSET THEN BEGIN
REPEAT
l_RecWhseActLines.RESET;
l_RecWhseActLines.SETRANGE("Activity Type",l_RecWhseActHdr.Type);
l_RecWhseActLines.SETRANGE("No.",l_RecWhseActHdr."No.");
IF l_RecWhseActLines.FINDSET THEN BEGIN
l_RecData.INIT;
REPEAT
l_RecData.Date := WORKDATE;
l_RecData.Time := TIME;
l_RecData.PutAwayCount += 1;
UNTIL l_RecWhseActLines.NEXT = 0;
END;
l_RecData.User := l_RecWhseActHdr."Assigned User ID";
l_RecData.INSERT(TRUE);
UNTIL l_RecWhseActHdr.NEXT = 0;
END;

If I do the same code for Picks the estimated number of lines would be 750000...
Here's an exemple of what the table (l_RecData) shows (not the real data):

USER - PutAwayCount
1 4
1 4
1 7
2 10

Here's what I want:

USER - PutAwayCount
1 15
2 10

I want the User to be unique and his fields summed like in SQL.

I tried to do this with a Query and worked for Picks or Put-Aways, not both. Because I needed two COUNT columns for the chart to show by user.

vaprog

Slawek's observation aside, you should be able to use a Query for picks and put-aways each quite efficiently.
NAV does not offer any way to do a UNION, other than iteration (kind of like you did in the code posted above, but only use the common fields in the key (User, in this case), and modify the existing record if one exists already). You can do exactly this with the two Query results.

By the way: please use Code tags when you post code. Its so much more readable if it is indented and syntax highlighted.

Juhl

Best solution performance wise, is creating the userfield in line table, fill it, create a key on lines table starting with userfield and then other fields to be used in the filter. Then in usertable create a flowfield of type count against the line table. Then you have instant data. But it requires changes.

You could also add the count flowfield to the header, and count from there, but I don't know if that helps on performance.

But keys in NAV is your friend, when handling large amounts of data.

Slawek_Guzek

Hi,

If I understood correctly you want to have a graph showing number of pick and put-aways per user on the same chart.

Looking at your numbers makes me think it may not make sense. You've mentioned 750 000 picks and 300+ put-aways. If you have, say a 100 users, the average number of picks per user will be 7500 and an average number of put-aways will be 3.

If you put both numbers on the same graph the number of put-aways will look like a pancake lying flat on the X axis, always - no matter what. It will be graphically meaningless.


Slawek

vaprog

Slawek's observation aside, you should be able to use a Query for picks and put-aways each quite efficiently.
NAV does not offer any way to do a UNION, other than iteration (kind of like you did in the code posted above, but only use the common fields in the key (User, in this case), and modify the existing record if one exists already). You can do exactly this with the two Query results.

By the way: please use Code tags when you post code. Its so much more readable if it is indented and syntax highlighted.

MarkKs

vaprog wrote: »

Slawek's observation aside, you should be able to use a Query for picks and put-aways each quite efficiently.
NAV does not offer any way to do a UNION, other than iteration (kind of like you did in the code posted above). You can do exactly this with the two Query results.

By the way: please use Code tags when you post code. Its so much more readable if it is indented and syntax highlighted.

So you suggest to create to separated queries for two different charts. Well it might help

MarkKs

Slawek_Guzek wrote: »

Hi,

If I understood correctly you want to have a graph showing number of pick and put-aways per user on the same chart.

Looking at your numbers makes me think it may not make sense. You've mentioned 750 000 picks and 300+ put-aways. If you have, say a 100 users, the average number of picks per user will be 7500 and an average number of put-aways will be 3.

If you put both numbers on the same graph the number of put-aways will look like a pancake lying flat on the X axis, always - no matter what. It will be graphically meaningless.


Slawek

You're right, it does not make sense and it's kinda of mess mixing Picks and Put-aways in per User in the same graph. As @vaprog said doing two queries might be the best way of doing this.

Slawek_Guzek

By the way can you explain why do you need to count lines, why counting headers is not enough?

You have the Type field in the header so you could pull the number of Picks/Put Aways per user only using the header.

Counting 750k lines is a big job, even for SQL.

MarkKs

Slawek_Guzek wrote: »

By the way can you explain why do you need to count lines, why counting headers is not enough?

You have the Type field in the header so you could pull the number of Picks/Put Aways per user only using the header.

Counting 750k lines is a big job, even for SQL.

Well a header can have more than one line, but a line can only have one Header. There's no field I can use to count the lines using the header, and counting the headers is not the right solution. I would need both Header and Lines to count the Lines per Header.

This is a Productivity Chart (divided in two) so I want to know what each user did until now, how many picks and how many put-aways. Even being a big job for SQL, i'm using now two queries for Put-Aways and for Picks in separated charts, I don't see better way of doing this.

Slawek_Guzek

My point is that you are comparing a number of pick lines which may be very different.

This all depends on your data of course, but not assuming anyting I can imagine that you can have a pick line with one large item, and another line with 100 of items, another line also with a 100 of items packed in one box, and yet another line with 100 of items in 25 boxes 4 each.

Do they really compare?

It's just a thought.

Slawek

MarkKs

Slawek_Guzek wrote: »

My point is that you are comparing a number of pick lines which may be very different.

This all depends on your data of course, but not assuming anyting I can imagine that you can have a pick line with one large item, and another line with 100 of items, another line also with a 100 of items packed in one box, and yet another line with 100 of items in 25 boxes 4 each.

Do they really compare?

It's just a thought.

Slawek

I've confirmed that, It appears that I was using the wrong tables, instead I could've just used one that currently have the right data. Thank you for all the help