show all value from both table

[omid5ive]

hi

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?

[Chanty4u]

Hi

is this ?

A:

load * Inline

[

Product    ,stateCode

a,    1

a,    2

b,    2

c,    3

];

right Join(A)

B:

LOAD * Inline

[

statecode    ,value

1    ,10

2,    20

3,    30

];

[Anonymous]

May be a worst possible way to do it..

B:

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

Product,stateCode

a,1

b,2

c,3

a,2]

;

A:

Load distinct Product as p1 Resident B;

left join

Load * inline [

statecode,value

1,10

2,20

3,30];

C:

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;

[qlikview979]

May be this

A:

load * Inline

[

Product    ,stateCode

a,    1

a,    2

b,    2

c,    3

];

Right Join(A)

B:

LOAD * Inline

[

statecode    ,value

1    ,10

2,    20

3,    30

];

[Kushal_Chawda]

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;