Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

firstsortedvalue in script gives 'Invalid Expression Error'

Hi,

i am trying to retrieve top record in table for each batch

Batchcampaign_id
123
212
313
234
223
120
121
310

Now i need to get as below

Batchcampaign_id
123
234
313

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 ;

8 Replies

Re: firstsortedvalue in script gives 'Invalid Expression Error'

Hi,

You need group by.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
berryandcherry6
Contributor II

Re: firstsortedvalue in script gives 'Invalid Expression Error'

Hi,

Group by is in my Script

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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

Life is so rich, and we need to respect to the life !!!

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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,

Great dreamer's dreams never fulfilled, they are always transcended.

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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.

Life is so rich, and we need to respect to the life !!!
sasiparupudi1
Honored Contributor III

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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;

Highlighted
berryandcherry6
Contributor II

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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?

Re: firstsortedvalue in script gives 'Invalid Expression Error'

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

Life is so rich, and we need to respect to the life !!!
Community Browser