Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

Showing UnKnown error in script loader

HI,

i have below code

LOAD

campaign_id

RESIDENT CAMPAIGN where campaign_start = Date(Num(Max(campaign_start, 2))) and match(camp_batch_meta_data_id ,24);

here i am trying to retrieve 'campaign_id '  where  camp_batch_meta_data_id  = 24 and campaign_start  date will be  2nd largest in table.

If i load this i am getting Unknown error.

What i am doing wrong. Please help me on this

Update: Please check Sunny's answers. This can be done both in load editor and front end as per requirement. Both works fine.

1 Solution

Accepted Solutions
sunny_talwar

I see you renamed the Max(campaign_redirect_start, 2)

Try this:

Table:

LOAD campaign_id as max_campaign_start,

          campaign_redirect_start

Resident CAMPAIGN

Where Match(camp_batch_meta_data_id ,24);

Right Join (Table)

LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start campaign_redirect_start

Resident Table;

So instead of using max_campaign_redirect_start use campaign_redirect_start

View solution in original post

11 Replies
sunny_talwar

You are not allowed to use Aggregation functions such as Max in the Where Statement. You might want to do something like this:

Table:

LOAD campaign_id,

          campaign_start

Resident CAMPAIGN

Where Match(camp_batch_meta_data_id ,24);

Right Join (Table)

LOAD Max(campaign_start, 2) as campaign_start

Resident Table;

berryandcherry6
Creator II
Creator II
Author

Hi Sunny,

Thanks for reply

Now it is not showing any error, but it shows all campaign_id of camp_batch_meta_data_id = 24

Basically, what i am trying is to get latest 2nd campaign_id in batch 24, for that

First i filter CAMPAIGN table with  camp_batch_meta_data_id = 24 and then in that(filtered table), i check for campaign_start,  2nd highest date to get 2nd latest date using Max(campaign_start, 2) .

But it showing all campaign_id for batch 24

sunny_talwar

Can you share the exact script you are using? Did you use Right Join?

berryandcherry6
Creator II
Creator II
Author

HI,

LOAD `campaign_id`,

`batch_meta_data_id` as camp_batch_meta_data_id,

`campaign_id` as campaign_name_redirect,

`start_date` as campaign_redirect_start,

    `campaign_name`;

SQL SELECT `campaign_id`,

`start_date`,

`batch_meta_data_id`,

    `campaign_name`

FROM CAMPAIGN;

Table:

LOAD campaign_id as max_campaign_start,

          campaign_redirect_start

Resident CAMPAIGN

Where Match(camp_batch_meta_data_id ,24);

Right Join (Table)

LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start

Resident Table;

I am exactly using as above,

sunny_talwar

I see you renamed the Max(campaign_redirect_start, 2)

Try this:

Table:

LOAD campaign_id as max_campaign_start,

          campaign_redirect_start

Resident CAMPAIGN

Where Match(camp_batch_meta_data_id ,24);

Right Join (Table)

LOAD Max(campaign_redirect_start, 2) as max_campaign_redirect_start campaign_redirect_start

Resident Table;

So instead of using max_campaign_redirect_start use campaign_redirect_start

berryandcherry6
Creator II
Creator II
Author

Thanks, It worked

berryandcherry6
Creator II
Creator II
Author

Hi,

one more thing, instead of using in script, can i add this condition in front end?

If so, how could i do that?

sunny_talwar

You will need to provide more details for me to give you an answer for this. You are wanting to see this in a chart? What are your dimensions and what are your expressions?

berryandcherry6
Creator II
Creator II
Author

HI Sunny,

Here i need to get counts of landing hits according to campaigns,batches and latest date of batch(to get first and second largest campaign) and update in table.

Here i am just using expression to get counts/sum and displaying in table

currently i am using

sum({$<batch_meta_data_id={'42''}>} landing_hits)

Now i need to apply condition mentioned in script, to expression in front end

How could i apply script condition in front end expression?

I could provide more information, if my provided information is not clear

Regards,

Supriya