Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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
];
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;
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
];
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;