Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have my data source table in this format:
Customer ID | Category Type | Category Value |
1 | A | A1 |
2 | A | A2 |
3 | A | A3 |
4 | A | A4 |
1 | B | B1 |
2 | B | B1 |
3 | B | B2 |
4 | B | B3 |
1 | C | C1 |
2 | C | C2 |
3 | C | C3 |
4 | C | C4 |
5 | C | C1 |
and while loading the data in QV I want to load it in the following format:
Customer ID | A | B | C |
1 | A1 | B1 | C1 |
2 | A2 | B1 | C2 |
3 | A3 | B2 | C3 |
4 | A4 | B3 | C4 |
5 | - | - | C1 |
Any ideas?
T1:
LOAD * INLINE [
CustomerID, CategoryType, CategoryValue
1, A, A1
2, A, A2
3, A, A3
4, A, A4
1, B, B1
2, B, B1
3, B, B2
4, B, B3
1, C, C1
2, C, C2
3, C, C3
4, C, C4
5, C, C1
];
T2:
LOAD
CustomerID,
MaxString(If(CategoryType = 'A', CategoryValue)) AS A,
MaxString(If(CategoryType = 'B', CategoryValue)) AS B,
MaxString(If(CategoryType = 'C', CategoryValue)) AS C
Resident T1
Group By CustomerID;
DROP Table T1;
JG
T1:
LOAD * INLINE [
CustomerID, CategoryType, CategoryValue
1, A, A1
2, A, A2
3, A, A3
4, A, A4
1, B, B1
2, B, B1
3, B, B2
4, B, B3
1, C, C1
2, C, C2
3, C, C3
4, C, C4
5, C, C1
];
T2:
LOAD
CustomerID,
MaxString(If(CategoryType = 'A', CategoryValue)) AS A,
MaxString(If(CategoryType = 'B', CategoryValue)) AS B,
MaxString(If(CategoryType = 'C', CategoryValue)) AS C
Resident T1
Group By CustomerID;
DROP Table T1;
JG