Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

berryandcherry6
Contributor II

Loading rows according to if else condition in script

Hi,

I have a table like below in script

CustomCampaignTable:

Noconcatenate

Load client_id,

batch_meta_data_id,

campaign_id,

campaign_name,

expiration_date,

expFlag,

last_batch

resident MainTable where last_batch = 1 OR expFlag = 0;

Drop Table MainTable;

which gives me this data rows

client_idbatch_meta_data_idcampaign_idcampaign_nameexpiration_dateexpFlaglast_batch
12781061Campaign_04-26-2017_05-14-20172017-05-1411
12791060Campaign_04-26-2017_05-14-20172017-05-1411
12801111Campaign_05-17-2017_06-04-20172017-06-0411
12801142Campaign_05-31-2017_06-18-20172017-06-180-
12801158Campaign_06-07-2017_06-25-20172017-06-250-
12811110Campaign_05-17-2017_06-04-20172017-06-0411
12811141Campaign_05-31-2017_06-18-20172017-06-180-
12811157Campaign_06-07-2017_06-25-20172017-06-250-

Now i want to load rows with expFlag =0, if (count of rows with expFlag = 1 and last_batch = 1) >= 2. else load all rows in table .

i want this datas  according to example


 

client_idbatch_meta_data_idcampaign_idcampaign_nameexpiration_dateexpFlaglast_batch
12801142Campaign_05-31-2017_06-18-20172017-06-180-
12801158Campaign_06-07-2017_06-25-20172017-06-250-
12811141Campaign_05-31-2017_06-18-20172017-06-180-
12811157Campaign_06-07-2017_06-25-20172017-06-250-


How could i do this? Please help me on this.

Tags (1)
1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Loading rows according to if else condition in script

HI,

try this

Table:
LOAD * Inline [
client_id batch_meta_data_id campaign_id campaign_name expiration_date expFlag last_batch
12 78 1061 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
12 79 1060 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
12 80 1111 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
12 80 1142 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
12 80 1158 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
12 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
12 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
12 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
13 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
13 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
13 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
]
(delimiter is spaces);

Left Join LOAD client_id,Sum(If(expFlag=1 and last_batch=1,1,0)) as Count
Resident Table Group By client_id;
Left Join LOAD *,If(Count >= 2,If(expFlag=0,1,0),1) as Count1
Resident Table;
NoConcatenate LOAD * Resident Table
Where Count1 = 1;
Drop Table
Table;

Regards,

Antonio

3 Replies
ahaahaaha
Honored Contributor

Re: Loading rows according to if else condition in script

Hi,

I correctly understand that

Now i want to load rows with expFlag =0, if (count of rows with expFlag = 1 and last_batch = 1) >= 2. else load all rows in table .

is true for each [client_id] separately?

berryandcherry6
Contributor II

Re: Loading rows according to if else condition in script

Hi Andrey,

Yes, you are right. It has to for each [client_id] separately.

antoniotiman
Honored Contributor III

Re: Loading rows according to if else condition in script

HI,

try this

Table:
LOAD * Inline [
client_id batch_meta_data_id campaign_id campaign_name expiration_date expFlag last_batch
12 78 1061 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
12 79 1060 Campaign_04-26-2017_05-14-2017 2017-05-14 1 1
12 80 1111 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
12 80 1142 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
12 80 1158 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
12 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
12 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
12 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
13 81 1110 Campaign_05-17-2017_06-04-2017 2017-06-04 1 1
13 81 1141 Campaign_05-31-2017_06-18-2017 2017-06-18 0 -
13 81 1157 Campaign_06-07-2017_06-25-2017 2017-06-25 0 -
]
(delimiter is spaces);

Left Join LOAD client_id,Sum(If(expFlag=1 and last_batch=1,1,0)) as Count
Resident Table Group By client_id;
Left Join LOAD *,If(Count >= 2,If(expFlag=0,1,0),1) as Count1
Resident Table;
NoConcatenate LOAD * Resident Table
Where Count1 = 1;
Drop Table
Table;

Regards,

Antonio

Community Browser