topic Re: Group by in script in QlikView

Group by in script

capriconuser — Tue, 12 Mar 2019 06:00:22 GMT

I am trying to load script with group by and fetch current date by Today() function .. here is what i load the script

PO:
Qulaify *;
Unqualify %Key;

Load

Today() as current_Date,
[applicat ac] as account_no,
[co code] as %Key,
ID as Refrence,
IF(Left(ID,2)='PO','PO',NULL()) AS PO,
[Code Bra] as Code_Bran
Date(Date#([ISS DATE],'yymmddhhmm')) as DATE,
sum([iss amount]) as ISS_AMOUNT
from 
[..\Extraction\po_table] (qvd)
group by 
current_Date, [applicat ac], [co code], ID, [Code Bra] , [ISS DATE] , [iss amount];

Store PO into PO(qvd);
Unqualify ;

Now here when i load the script this shows that Field "Current_Date" not found and here i aliases this field on Today() function

secondly when i comment this Today() function from load and group by then error occurred " Invalid expression" and not exactly mentioned where invalid expression is

any help about this ?

Re: Group by in script

MindaugasBacius — Tue, 12 Mar 2019 06:35:17 GMT

Use this:
Group By
Today(), [applicat ac], [co code], ID, IF(Left(ID,2)='PO','PO',NULL()), [Code Bra], Date(Date#([ISS DATE],'yymmddhhmm'))

Re: Group by in script

capriconuser — Tue, 12 Mar 2019 06:43:21 GMT

for this "Date(Date#([ISS DATE],'yymmddhhmm'))" can we use just ISS DATE?

and what this sum([iss amount]) as ISS_AMOUNT ? in database [iss amount] data type is varchar so i convert to int in script ?

Re: Group by in script

shiveshsingh — Tue, 12 Mar 2019 08:49:25 GMT

Try with Resident load instead, your alias will work in group by then