Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
How do I convert this table
A. B C D
a 1/1/2019 1.2 -
a 1/1/2019 - 0.4
a 2/1/2019 2.2 -
a 2/1/2019 - 2.5
a 3/1/2019 3.2 -
a 3/1/2019 - 5.6
b 5/6/2019 2.6 -
b 5/6/2019 - 7.3
b 6/6/2019 7.7 -
b 6/6/2019 - 6.8
b 7/6/2019 7.8 -
b 7/6/2019 - 3.3
into
EITHER
A. newcolumn B C D
a 1/1/2019 1/1/2019 1.2 -
a 1/1/2019 1/1/2019 - 0.4
a 1/1/2019 2/1/2019 2.2 -
a 1/1/2019 2/1/2019 - 2.5
a 1/1/2019 3/1/2019 3.2 -
a 1/1/2019 3/1/2019 - 5.6
a 2/1/2019 2/1/2019 2.2 -
a 2/1/2019 2/1/2019 - 2.5
a 2/1/2019 3/1/2019 3.2 -
a 2/1/2019 3/1/2019 - 5.6
a 3/1/2019 3/1/2019 3.2 -
a 3/1/2019 3/1/2019 - 5.6
.....
same here for b
-----
OR
Same as above, but D is also in each row replacing "-" value.
This seems to work
Table:
LOAD * INLINE [
A, B, C, D
a, 1/1/2019, 1.2, -
a, 1/1/2019, -, 0.4
a, 2/1/2019, 2.2, -
a, 2/1/2019, -, 2.5
a, 3/1/2019, 3.2, -
a, 3/1/2019, -, 5.6
b, 5/6/2019, 2.6, -
b, 5/6/2019, -, 7.3
b, 6/6/2019, 7.7, -
b, 6/6/2019, -, 6.8
b, 7/6/2019, 7.8, -
b, 7/6/2019, -, 3.3
];
Left Join (Table)
LOAD A,
B as NewCol,
C as NewC,
D as NewD
Resident Table;
FinalTable:
NoConcatenate
LOAD A,
B as NewCol,
NewC as C,
NewD as D,
NewCol as B
Resident Table
Where NewCol >= B;
DROP Table Table;
You can try this
Table:
LOAD * INLINE [
A, B, C
a, 1/1/2019, 1.2
a, 2/1/2019, 2.2
a, 3/1/2019, 3.2
b, 5/6/2019, 2.6
b, 6/6/2019, 7.7
b, 7/6/2019, 7.8
];
Left Join (Table)
LOAD A,
B as NewCol
Resident Table;
FinalTable:
NoConcatenate
LOAD A,
B,
C,
NewCol
Resident Table
Where NewCol >= B;
DROP Table Table;
Thanks for the quick response.
It makes the 'b' to contain values of "B" which corresponds to 'a'.
I am not sure if I can use INLINE as I have the data as a table already.
Thank you.
Yup... not expecting for you to use Inline Table... you just need to use your original table and then add everything after Left Join.... Here is an updated script
Left Join (Table)
LOAD A,
B as NewCol,
C as NewC
Resident Table;
FinalTable:
NoConcatenate
LOAD A,
B as NewCol,
NewC as C,
NewCol as B
Resident Table
Where NewCol >= B;
DROP Table Table;
Running it in QlikView gave me this
Hi, really thank you for the help.
I see both of ur codes are partially working. Just that values of C are not in right place.
I think the mistake is on my side. I have updated the data model I have. Does ur code still work for the new datamodel?
Thanks so much.
This seems to work
Table:
LOAD * INLINE [
A, B, C, D
a, 1/1/2019, 1.2, -
a, 1/1/2019, -, 0.4
a, 2/1/2019, 2.2, -
a, 2/1/2019, -, 2.5
a, 3/1/2019, 3.2, -
a, 3/1/2019, -, 5.6
b, 5/6/2019, 2.6, -
b, 5/6/2019, -, 7.3
b, 6/6/2019, 7.7, -
b, 6/6/2019, -, 6.8
b, 7/6/2019, 7.8, -
b, 7/6/2019, -, 3.3
];
Left Join (Table)
LOAD A,
B as NewCol,
C as NewC,
D as NewD
Resident Table;
FinalTable:
NoConcatenate
LOAD A,
B as NewCol,
NewC as C,
NewD as D,
NewCol as B
Resident Table
Where NewCol >= B;
DROP Table Table;
Awesome. It works. Thank you.