topic Re: Loading table with appending extra column and Applying Condition in QlikView

Loading table with appending extra column and Applying Condition

berryandcherry6 — Thu, 23 Feb 2017 06:49:39 GMT

Hi,

Assume i have a data like below

Batch_id	Campaign_id	Campaign_name
55	52	camp_bc
55	53	camp_cd
55	51	camp_ab
56	62	camp_bc
57	71	camp_ab
55	54	camp_ef
58	81	camp_ab
56	61	camp_ab
54	91	camp_ab
57	22	camp_bc

I want to create a script to order batch_id in Ascending , campaign_id in Descending, and to create a one more column called brand_name with latest campaign_id with brand_1, second latest as brand_2 etc.. as shown in below figure

Batch_id	Campaign_id	Campaign_name	brand_name
54	91	camp_ab	brand_1
55	54	camp_ef	brand_4
55	53	camp_cd	brand_3
55	52	camp_bc	brand_2
55	51	camp_ab	brand_1
56	62	camp_bc	brand_2
56	61	camp_ab	brand_1
57	72	camp_bc	brand_2
57	71	camp_ab	brand_1
58	81	camp_ab	brand_1

I have below code currently

let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

LOAD `campaign_id`,

`campaign_group_id`,

`batch_meta_data_id`,

`start_date` as Campaign_Start_Date,

date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,

`campaign_name` where expiration_date >= '$(varExpirationDate)';

SQL SELECT `campaign_id`,

`start_date`,

`batch_meta_data_id`,

`campaign_group_id`,

`campaign_name`,

expiration_date

FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;

Re: Loading table with appending extra column and Applying Condition

sunny_talwar — Thu, 23 Feb 2017 10:22:46 GMT

May be this:

let varExpirationDate = Timestamp(Today(),'YYYY-MM-DD hh:mm:ss');

Table:

LOAD `campaign_id`,

`campaign_group_id`,

`batch_meta_data_id`,

`start_date` as Campaign_Start_Date,

date(floor(expiration_date),'MM-DD-YYYY') as expiration_date,

`campaign_name` where expiration_date >= '$(varExpirationDate)';

SQL SELECT `campaign_id`,

`start_date`,

`batch_meta_data_id`,

`campaign_group_id`,

`campaign_name`,

expiration_date

FROM CAMPAIGN where batch_meta_data_id IN ('54','55','56','57','58') Order by campaign_id desc;

FinalTable:

LOAD *,

'brand_' & AutoNumber(Campaign_id, batch_meta_data_id) as brand_name

Resident Table

Order By batch_meta_data_id, Campaign_id;

DROP Table Table;

Re: Loading table with appending extra column and Applying Condition

berryandcherry6 — Thu, 23 Feb 2017 11:39:47 GMT

Hi Sunny,

i am getting sync error.

Re: Loading table with appending extra column and Applying Condition

sunny_talwar — Thu, 23 Feb 2017 12:07:22 GMT

Did you forget to drop the Table?

Re: Loading table with appending extra column and Applying Condition

berryandcherry6 — Thu, 23 Feb 2017 12:12:17 GMT

Thanks Sunny, that was quick needed response.

Re: Loading table with appending extra column and Applying Condition

berryandcherry6 — Thu, 23 Feb 2017 12:20:27 GMT

Hi Sunny,

one more thing,

Here instead of batch_1, batch 2 ...., i need to keep it as current week, latest week, second latest week.

How could i do that. But it has to be different words for diferent brand_name

Re: Loading table with appending extra column and Applying Condition

sunny_talwar — Thu, 23 Feb 2017 12:21:46 GMT

Can you explain by amending your above example?

Re: Loading table with appending extra column and Applying Condition

berryandcherry6 — Thu, 23 Feb 2017 12:28:06 GMT

Because in our services, we usually show data for campaigns. Some times these campaigns will be running for 1 week, 2 week, 3 week ... so at this time i need to display them whether they are Current Week, Previous Week or Last Week.

Re: Loading table with appending extra column and Applying Condition

sunny_talwar — Thu, 23 Feb 2017 12:32:51 GMT

That's not what I asked for.... You shared this in your initial post

I guess you want to change brand_name to something else now? Is that right?