topic Re: composite key in QlikView

composite key

kunkumnaveen — Wed, 28 Jun 2017 06:41:13 GMT

Hello All,

let say i got two table with data as below,

Table1:

Supplier Material plant

s1 A A001

s2 B A002

s1 C A003

Table2:

z_part component plant

z1 A A001

z2 B A002

so linking these tables by creating a composite key

which is material&plant as Key from table1

component&plant as Key from table2

finally what i get is this

Key

AA001

BA002

CA003

what i am looking is i need a new column ,which should compare with z_part field ,i mean if there is a value in z_part field against that particular Key then new column value should be that z_part value else it should be Material value from Table1

Final Output I need is

Key New_Cloumn

AA001 z1

BA002 z2

CA003 C

from above table CA003 key values has not z_part value so in new column field the value is C from material field

how can i do this at script level

Thanks

Re: composite key

kunkumnaveen — Wed, 28 Jun 2017 07:02:35 GMT

Any Suggestion in achieving this requirement please....

Re: composite key

antoniotiman — Wed, 28 Jun 2017 07:14:18 GMT

Hi Naveen,

see Attachment.

Regards,

Antonio

Re: composite key

Kushal_Chawda — Wed, 28 Jun 2017 07:19:41 GMT

Table2:

mapping LOAD component&plant as Key,

z_part

Inline [

z_part, component, plant

z1, A , A001

z2, B , A002 ];

Table1:

LOAD *,

if(Z_Part1='NA',Material,Z_Part1) as Z_Part;

LOAD *,ApplyMap('Table2',Material&plant,'NA') as Z_Part1

Inline [

Supplier, Material, plant

s1, A , A001

s2, B, A002

s1, C , A003 ];

Re: composite key

kunkumnaveen — Wed, 28 Jun 2017 07:43:13 GMT

HI,thanks for reply could u plz copy paste the script please ,as i am not allow to copy any qvw files in server...plz

thanks

Re: composite key

antoniotiman — Wed, 28 Jun 2017 07:46:44 GMT

Table1:
LOAD * Inline [
Supplier Material plant
s1 A A001
s2 B A002
s1 C A003
](delimiter is spaces);
Table2:
Join
LOAD * Inline [
z_part component plant
z1 A A001
z2 B A002
](delimiter is spaces);

LOAD Material&plant as Key,If(IsNull(z_part),Material,z_part) as New_Column
Resident Table1;
Drop Table Table1;

Re: composite key

Kushal_Chawda — Wed, 28 Jun 2017 07:48:25 GMT

Map:

mapping LOAD component&plant as Key,

z_part

FROM table 2;

Data:

LOAD *,

if(Z_Part1='NA',Material,Z_Part1) as Z_Part;

LOAD Supplier,

ApplyMap('Map',Material&plant,'NA') as Z_Part1

Material,

Plant

FROM table1;