Group by statement not working

rajan_07kumar — Thu, 22 Sep 2016 13:50:01 GMT

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]

;

Re: Group by statement not working

Gysbert_Wassenaar — Thu, 22 Sep 2016 16:24:53 GMT

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.

Re: Group by statement not working

rajan_07kumar — Wed, 28 Sep 2016 06:22:34 GMT

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.

topic Re: Group by statement not working in QlikView

Group by statement not working

Re: Group by statement not working

Re: Group by statement not working