topic Make a new table according to a template table in QlikView

Make a new table according to a template table

uacg0009 — Thu, 26 Dec 2013 07:38:45 GMT

Hi everyone,

I have two tables like these:

and I want to get a new table according to the table2 in Script, so the table2 is just like a template table. What I want to get finally is:

PG41 03 A1 01 HQ

PG41 03 A2 01 HQ

PG42 03 A1 02 CN

PG42 03 A2 03 JP

I want this in my script, actually the first four columns is CD_PLANT, CD_OFFICE, TX_BRAND, TX_CUSTOMER

and the blank of the column BRAND or CUSTOMER in table2 means all TX_BRAND or all TX_CUSTOMER data fit the situation. So

the fifth column what I want to get is the SUBSIDIARY of the table2. Every row in table1 will always find a template in table2 to

match.

So I hope I have clarified the problem. The attached file is the data what I have created.

Thank you all!

Re: Make a new table according to a template table

Thu, 26 Dec 2013 08:30:41 GMT

Just left join these 2 tables? Is this what you want for final result?

Re: Make a new table according to a template table

uacg0009 — Thu, 26 Dec 2013 08:55:15 GMT

Thank you but that is not what I want.

I have said that what I want for final result is data like this below:

PG41 03 A1 01 HQ

PG41 03 A2 01 HQ

PG42 03 A1 02 CN

PG42 03 A1 03 CN

the TX_SUBSIDIARY needs to change to one of the SUBSIDIARY according to the PLANT, OFFICE,

BRAND,CUSTOMER in table2 and CD_PLANT, CD_OFFICE, TX_BRAND, TX_CUSTOMER match or not.

If you have any problem pls tell me.

Re: Re: Make a new table according to a template table

Thu, 26 Dec 2013 09:45:09 GMT

You can use different ApplyMap() function calls with different keys. This way you can nest from the most specific to the most general combination of fields. Attached you have an example, maybe not exactly the one you want but you can easily adapt it.

Re: Make a new table according to a template table

brijesh1991 — Thu, 26 Dec 2013 11:00:11 GMT

Use ApplyMap:

map:

mapping Load PALNT, SUBSIDIARTY from T2;

Table1:

Load *, ApplyMap('map',CD_PLANT,null()) as SUBSIDIARTY from T1;

It will map subsidiary field from T2 to T1. . .

Re: Make a new table according to a template table

uacg0009 — Fri, 27 Dec 2013 08:26:04 GMT

Sorry for the reply late and thank you, but the result is not what I want.

The result what I want is :

PG41 03 A1 01 HQ

PG41 03 A2 01 HQ

PG42 03 A1 02 CN

PG42 03 A2 03 JP

The fifth column(SUBSIDIARY) in new table should refer to BRAND and CUSTOMER in T2 in this example.

The blank value in column BRAND or CUSTOMER means all values is ok, is matched.

Hope you know what I want, Thanks!

Re: Make a new table according to a template table

uacg0009 — Fri, 27 Dec 2013 08:41:29 GMT

Sorry for the reply late, I changed the script that you gave me a little,

by now it is correct and thank you so much!

Re: Make a new table according to a template table

brijesh1991 — Fri, 27 Dec 2013 11:16:42 GMT

Hi Zhao,

You need to create an explicit if..else if conditon to create such field.

join or applyMap won't help in your case

Regards,

Brijesh