Loading rows according to if else condition in script

[berryandcherry6]

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_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	-

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_id	batch_meta_data_id	campaign_id	campaign_name	expiration_date	expFlag	last_batch
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	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	-

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

[ahaahaaha]

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]

Hi Andrey,

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

[antoniotiman · Accepted solution]

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