Group by statement not working

[rajan_07kumar]

Hi all,

when I try to reload the below script with group by, it doesnt happen and the logfile shows only till SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; this point. load statement doesnt even start executing. I ve checked all the selected fields are present in the group by clause. My file D:\Qlikview\GL_Ledger.qvd has 37 mln rows.

I tried with first 10 load but still no luck. Please help !

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

GL_SL_LEDGER:

LOAD

[Link_Cost_CentreID],

[Link_GL_Account],

[Link_DivisionID],

[Link_LocationID],

[Link_ProductID],

[Link_Date],

[Link_Inter_CompanyID],

[GL Account],

[Trx Type],

[COA_Version],

[CC_GL_Flag],

[Account_Head],

[Account_Sub_Head],

[Notes_CC],

[Account_Sub_Sub_Head],

[Account_Level4],

[Period Name],

[Old_GL_Account],

[Old Description],

[JE Batch ID],

[JE Header ID],

[JE Line Number],

[JE Category],

[JE Source],

[Last Updated Month],

[GL Date],

[GL Document NO],

[Invoice No.],

[Invoice Date],

[Supplier Code],

[Supplier],

[From_Bank],

[Product],

[Department],

[Description],

[Narration],

[Sale Description],

[Product_Nos],

[JulTrans_Flag],

[SepTrans_Flag],

sum([Debit Amount]) as [Debit Amount],

sum([Credit Amount]) as [Credit Amount],

sum([Opening Amount]) as [Opening Amount],

sum([Closing Amount]) as [Closing Amount],

sum([Net Amount]) as [Net Amount],

sum([Net Budget Amount]) as [Net Budget Amount],

sum([Sale_Volume]) as [Sale_Volume]

FROM

(qvd) Group by

[Link_Cost_CentreID],

[Link_GL_Account],

[Link_DivisionID],

[Link_LocationID],

[Link_ProductID],

[Link_Date],

[Link_Inter_CompanyID],

[GL Account],

[Trx Type],

[COA_Version],

[CC_GL_Flag],

[Account_Head],

[Account_Sub_Head],

[Notes_CC],

[Account_Sub_Sub_Head],

[Account_Level4],

[Period Name],

[Old_GL_Account],

[Old Description],

[JE Batch ID],

[JE Header ID],

[JE Line Number],

[JE Category],

[JE Source],

[Last Updated Month],

[GL Date],

[GL Document NO],

[Invoice No.],

[Invoice Date],

[Supplier Code],

[Supplier],

[From_Bank],

[Product],

[Department],

[Description],

[Narration],

[Sale Description],

[Product_Nos],

[JulTrans_Flag],

[SepTrans_Flag]

;

[Gysbert_Wassenaar]

when I try to reload the below script with group by, it doesnt happen and the logfile shows only till SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; this point. load statement doesnt even start executing.

Check the cpu usage. It's probably very busy calculating things. But you should ask yourself if you really need to do it this way. If your QVD file has only 37 records then you should be able to find a much smaller set of fields that will work for calculating the sum over. Likely you don't even need to sum any values, because there is no grouping by necessary, because there are 37 unique combination of all those field values. In other words no duplicate records if you disregard the fields you want to sum.

[rajan_07kumar]

Thanks Gysbert.

You were right in terms of CPU usage as the group by statement execution started 10 minutes after I started the reload. I just had to be a little patient and it went fine.

Also my data had 37000000 rows with 84 fields. I wanted only 44 in my final app and sum against those 44, which is why I used group by. It reduced the rows to 25000000.