Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
i am trying to retrieve top record in table for each batch
Batch | campaign_id |
1 | 23 |
2 | 12 |
3 | 13 |
2 | 34 |
2 | 23 |
1 | 20 |
1 | 21 |
3 | 10 |
Now i need to get as below
Batch | campaign_id |
1 | 23 |
2 | 34 |
3 | 13 |
i tried below script gives 'Invalid Expression' on load
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
LOAD `campaign_id`,
`campaign_group_id`,
`Batch`,
`start_date` as Campaign_Start_Date,
date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,
FirstSortedValue(Batch,campaign_id) as DataSorted,
`campaign_name`;
SQL SELECT `campaign_id`,
`start_date`,
`Batch`,
`campaign_group_id`,
`campaign_name`,
expiration_date
FROM CAMPAIGN where Batch IN (1,2,3) and expiration_date < '$(varExpirationDate)' group by Batch ;
Hi,
You need group by.
Regards,
Hi,
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;
Regards,
Check this code
let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');
Welcome:
LOAD * Inline [
Batch, campaign_id
1, 23
2, 12
3, 13
2, 34
2, 23
1, 20
1, 21
3, 10
];
NoConcatenate
MainTable:
LOAD
Batch,
MAX(campaign_id) as campaign_id
Resident Welcome Group By Batch Order By Batch;
Left Join
LOAD
Batch,
campaign_id
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
T1:
Load
campaign_id,
campaign_group_id,
start_date as Campaign_Start_Date,
Batch,
Date(Floor(expiration_date),'MM-DD-YYYY') as expiration_date,
campaign_name;
SQL SELECT
campaign_id,
start_date,
Batch,
campaign_group_id,
campaign_name,
expiration_date
FROM CAMPAIGN
Where Batch IN (1,2,3) and expiration_date < '$(varExpirationDate)';
Left Join(T1)
LOAD
Batch,
Max(campaign_id) as DataSorted
Resident T1
Group By Batch
Order By Batch;
Hi,
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
Welcome:
LOAD * Inline [
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
];
NoConcatenate
MainTable:
LOAD
Batch,
column1,
MAX(campaign_id) as campaign_id
Resident Welcome Group By Batch Order By Batch;
Can't i use like this?
1) You can use FirstSortedValue() by help of "Outer Join" and "Left Join"
Welcome:
LOAD * Inline [
Batch, campaign_id
1, 23
2, 12
3, 13
2, 34
2, 23
1, 20
1, 21
3, 10
];
Outer Join (Welcome)
LOAD
Batch, campaign_id
FROM (sharepoint project list) GROUP BY Batch
Left Join(Welcome)
LOAD,
Batch,
FirstSortedValue(campaign_id, Batch) as campaign_id1
Resident Welcome
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