firstsortedvalue in script gives 'Invalid Expression Error'

[berryandcherry6]

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 ;

[PrashantSangle]

Hi,

You need group by.

Regards,

[berryandcherry6]

Hi,

Group by is in my Script

[Anil_Babu_Samineni]

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

[PrashantSangle]

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,

[Anil_Babu_Samineni]

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.

[sasiparupudi1]

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;

[berryandcherry6]

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?

[Anil_Babu_Samineni]

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