Discussion board where members can get started with Qlik Sense.
i am trying to retrieve top record in table for each batch
Now i need to get as below
i tried below script gives 'Invalid Expression' on load
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
`start_date` as Campaign_Start_Date,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
FirstSortedValue(Batch,campaign_id) as DataSorted,
SQL SELECT `campaign_id`,
FROM CAMPAIGN where Batch IN (1,2,3) and expiration_date < '$(varExpirationDate)' group by Batch ;
You need group by.
Group by is in my Script
Bit buzy with work, Today morning i work with this. Check here same thread with different Fields nothing New
Re: Get Data with Max Value for each ID
You have to add all non aggregated field in group by clause.
Your group by clause is for sql script not for LOAD script;
Check this code
LOAD * Inline [
MAX(campaign_id) as campaign_id
Resident Welcome Group By Batch Order By Batch;
Resident Welcome Where Match(Batch,'1','2','3') and expiration_date < $(varExpirationDate);
Drop Table Welcome;
Note: I am assuming, expiration_date has same format of Variable.
Try like this
start_date as Campaign_Start_Date,
Date(Floor(expiration_date),'MM-DD-YYYY') as expiration_date,
Where Batch IN (1,2,3) and expiration_date < '$(varExpirationDate)';
Max(campaign_id) as DataSorted
Group By Batch
Order By Batch;
Here are two questions for you:
1) Why can not i use firstsorted value in script?When could i use it in script?
2.) if i have more columns than here, it gives in invalid expression
Batch, campaign_id, column1
1, 23, 1
2, 12, 1
3, 13, 2
2, 34, 1
2, 23, 1
1, 20, 3
1, 21, 1
3, 10, 1
Can't i use like this?
1) You can use FirstSortedValue() by help of "Outer Join" and "Left Join"
Outer Join (Welcome)
FROM (sharepoint project list) GROUP BY Batch
FirstSortedValue(campaign_id, Batch) as campaign_id1
Group By Batch;
2) Since, You are using Noconcatenate you won't get as expected. May be you can use Left join to achieve the same