topic Re: show all value from both table in QlikView

show all value from both table

omid5ive — Mon, 15 May 2017 07:28:47 GMT

i have two table like below:

Product	stateCode
a	1
b	2
c	3
a	2

and

statecode	value
1	10
2	20
3	30

i want to show value of each product in all state like below:

product	statecode	value
a	1	10
a	2	20
a	3	0
b	1	0
b	2	20
b	3	0
c	1	0
c	2	0
c	3	30

what should i do?

Re: show all value from both table

Chanty4u — Mon, 15 May 2017 07:34:21 GMT

is this ?

load * Inline

[

Product ,stateCode

a, 1

a, 2

b, 2

c, 3

];

right Join(A)

LOAD * Inline

[

statecode ,value

1 ,10

2, 20

3, 30

];

Re: show all value from both table

Anonymous — Mon, 15 May 2017 07:57:26 GMT

May be a worst possible way to do it..

Load *,Product&'|'&stateCode as Key Inline [

Product,stateCode

a,1

b,2

c,3

a,2]

;

Load distinct Product as p1 Resident B;

left join

Load * inline [

statecode,value

1,10

2,20

3,30];

Load *,p1&'|'&statecode as Key Resident A;

drop table A;

left join (C)

load * Resident B;

😧

Load p1 as Product,statecode,if(IsNull(stateCode),0,value) as value Resident C Order by p1;

drop table B,C;

Re: show all value from both table

qlikview979 — Mon, 15 May 2017 09:49:45 GMT

May be this

load * Inline

[

Product ,stateCode

a, 1

a, 2

b, 2

c, 3

];

Right Join(A)

LOAD * Inline

[

statecode ,value

1 ,10

2, 20

3, 30

];

Re: show all value from both table

Kushal_Chawda — Mon, 15 May 2017 12:39:07 GMT

see this

MapValue:

mapping LOAD

statecode,

Value

FROM Table2;

Data:

LOAD Product,

statecode,

ApplyMap('MapValue',statecode,Null()) as Value

FROM Table1;

New:

LOAD Distinct Product

Resident Data;

Left Join (New)

LOAD distinct statecode

FROM Table2;

Left Join (New)

LOAD Product,

statecode,

Value

Resident Data;

DROP Table Data;

Final:

NoConcatenate

LOAD Product,

statecode,

alt(Value,0) as Value

Resident New;

DROP Table New;