topic Re: Grouping by 3 columns or data issue? in QlikView

Grouping by 3 columns or data issue?

jcampbell474 — Sat, 25 Apr 2015 03:42:27 GMT

I am having an issue that I would normally attribute to a faulty join. But, it’s an aggregation of one table in the load script (I think). The sum of the quantity in the aggregated results table doesn’t match the quantity in ~5% of the records. In some instances, it’s doubled. In others, it’s tripled or more. I can’t recognize a pattern.

In one instance, there is a part that has two records in the first table. One of the records has a quantity of 10 and the other record’s quantity is null. The second table has one record with a quantity of 30. I added RowNo() and RecNo() to the load of the first table to see what might be hiding. The first table then had seven records, with three of them having a quantity of 10 (the other four are null), equaling 30. As filters, one DC and month is selected.

Why does the 1^st load only show a total quantity of 10 and two records, then after adding RecNo() and RowNo() – seven records with a total of 30? Why does the aggregation (group by’s) pick them up but they’re not visible without having RecNo() and RowNo()? I’m thinking it’s because the same part may have been received at several of the DC’s during several different months and it causes something to happen that I can’t recognize.

I have four columns, with three of them that need to be aggregated. The fourth column is quantity. If the quantity is a negative value, I change it to positive.

DC (12 DC’s total)
Month (12 months)
Part # (400K+)
Quantity (-/+)

Here is the load:
Table1:
oDC as c1DC,
oOrd_Month as c1Month,
Trim([oPart Number]) as [c1Part#],
if(oQuantity<0,(oQuantity)*-1,'') as c1ClsRtnQty

Aggregation:
Table2:
c1DC as cDC,
c1Month as cMonth,
[c1Part#] as cPart#,
sum(c1ClsRtnQty) as cClsRtnQty

Group by

c1DC,
c1Month,
[c1Part#]

I would post a sample app, but it’s a ton of data (~28mm rows) coming from one text file and I’ve got scattered pieces of sql everywhere. It would be lengthy chore just to get some sample data and recreate.

If anyone can point me in the right direction or tell me what I’m doing wrong, I would greatly appreciate it.

Many thanks in advance,

Jason

Re: Grouping by 3 columns or data issue?

swuehl — Sat, 25 Apr 2015 11:08:26 GMT

Jason,

it's hard for me to tell what the problem is just looking at the information provided.

It would be helpful to see at least the script part with the complete statements, e.g. by posting the relevant part of the qvw script execution log file.

You should not post any private data and not millions of records, there are some general guidelines around, like

Preparing examples for Upload - Reduction and Data Scrambling

First thing I noticed:

if(oQuantity<0,(oQuantity)*-1,'') as c1ClsRtnQty

-- in case of a positive oQuantity, shouldn't the value just be used in the else branch then?

if(oQuantity<0,(oQuantity)*-1,oQuantity) as c1ClsRtnQty

fabs(oQuantity) as c1ClsRtnQty

When you are talking about number of records being 2 before applying recno() and 7 after, how have you determined the number of records? When you've created a table box in the UI, please notice that a table box will only show distinct lines, so not showing duplicates.

In general, trying to shrink down an application to a minimum, still showing the issue, will help you to find the root cause (most of the time yourself, but in any case, it makes it much easier for the forum to help you).

Regards,

Stefan

Re: Grouping by 3 columns or data issue?

jcampbell474 — Sun, 26 Apr 2015 03:40:51 GMT

Thank you, Stefan. I think I managed to put a good sample app together that illustrates my issue(s).

In reference to your suggestion that I populate the quantity with a positive value instead of null if it's not a negative number, I can't do that as I need to have only negative numbers in that table. Positive numbers (Sales) in another table. The end result I'm looking for is:

(All records have to first be grouped by DC, then Month, then Part.)

1. If quantity is negative, then the entire record is categorized as a return. If it's positive, then it's a sale.

2. For each DC, the sales value had to be subtracted from the return value to arrive at the Carry-Over quantity. E.g., Part 123 has a record with a -5 quantity, and another record with a quantity of 10. The result of 5 means there was no Carry-Over as there were more sales than returns. On the other hand, Part 456 has a record with a quantity of -20, and another record with a quantity of 10. The result of -10 means that there was a quantity of 10 carried over to the next month in that DC.

I could quite possibly be going about this all wrong - open to any and all suggestions.

I hope this helps and you can point me in the right direction. Again, many thanks in advance!

Re: Grouping by 3 columns or data issue?

CELAMBARASAN — Sun, 26 Apr 2015 07:53:06 GMT

Hi,

To split sales and return, instead of if condition you could go with filtering it with where clause. By doing this other records gets suppressed instead of holding null value and records will get reduced in the table.

ClassReturnData7:

Load

//RowNo() as cRowNo,

//RecNo() as cRecNo,

oDC as c1DC,

oOrd_Month as c1Month,

Trim([oPart Number]) as [c1Part#],

(oQuantity)*-1 as c1ClsRtnQty

FROM (qvd)

Where oQuantity<0;

Store ClassReturnData7 Into ;

SalesData7:

Load

//RowNo() as sRowNo,

//RecNo() as sRecNo,

oDC as s1DC,

oOrd_Month as s1Month,

Trim([oPart Number]) as [s1Part#],

oQuantity as s1SalesQty

FROM (qvd)

Where oQuantity>0;

Store SalesData7 Into ;

Next is the Aggr table

ClassReturnsAggr:

Load

c1DC as cDC,

c1Month as cMonth,

[c1Part#] as cPart#,

sum(c1ClsRtnQty) as cClsRtnQty// you applied if condition to filter the data but you didn't applied here

//count(c1Part#) as cCount

FROM (qvd)

Where oQuantity<0// apply this where condition, you will get the desired output

Group by

c1DC,

c1Month,

[c1Part#];

Store ClassReturnsAggr Into ;

Re: Grouping by 3 columns or data issue?

jcampbell474 — Sun, 26 Apr 2015 16:14:27 GMT

Thank you!

I edited the script w/the changes you mentioned, but encountered a problem. I might not be understanding it correctly - here are my comments:

sum(c1ClsRtnQty) as cClsRtnQty// you applied if condition to filter the data but you didn't applied here

/ Do I need to apply it here? The If condition is applied in the preceding load, so would it be effective? /

Where oQuantity<0// apply this where condition, you will get the desired output

/ The field oQuantity is not present in the table being called. In addition, the quantity field in this column is cClsRtnQty, which was converted to a positive number in the preceding load. /

I tried a couple of Where Exists statements with no luck. I also removed all of the filters from the aggregation table and it replicated most records. Do you think I should not convert the negative numbers to positive in the first load, just use them to filter? Then, use them as a filter in the second load and convert to positive there?