Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I load the data and create a table like the following:
Product | Grade |
A | 1 |
B | 4 |
C | 3 |
D | 2 |
E | 5 |
F | 3 |
G | 2 |
H | 4 |
I | 1 |
But Actually I would like to add a column 'Team' which does not exist in my original dataset. And the table followed is what I want.
Team | Product | Grade |
X1 | A | 1 |
X2 | B | 4 |
X3 | C | 3 |
D | 2 | |
X4 | E | 5 |
F | 3 | |
G | 2 | |
X5 | H | 4 |
X6 | I | 1 |
What should I do?
Orig:
LOAD * Inline [
Product, Grade
A, 3
B, 5
C, 2
D, 1
E, 5
F, 4
G, 1
H, 3
I, 2
];
Final:
NoConcatenate
LOAD
Product,
Grade,
If(Product='A','X1', If(Product='B','X2', If(WildMatch(Product,'C','D','E'),'X3', If(WildMatch(Product,'F','G','H','I'),'X4', 'XX' )))) as Team
Resident Orig;
DROP Table Orig;
I think that Team field is created according to a logic so when you load the original table:
LOAD
Product,
Grade,
if(mycondition, newField, null()) as Team
From ...
Hope it helps
May be I just give a wrong table..Sorry for that.
What I would like to create is like the following:
Team | Product | Grade |
---|---|---|
X1 | A | 3 |
X2 | B | 5 |
X3 | C | 2 |
D | 1 | |
E | 5 | |
X4 | F | 4 |
G | 1 | |
H | 3 | |
I | 2 |
Hi Jelly,
I suggest you that,
You Can Create Master Excel File for this & You can maintain @ 1 Place.
Which Contains Team & Product. & then you can Map That File with System Data.
Regards,
Orig:
LOAD * Inline [
Product, Grade
A, 3
B, 5
C, 2
D, 1
E, 5
F, 4
G, 1
H, 3
I, 2
];
Final:
NoConcatenate
LOAD
Product,
Grade,
If(Product='A','X1', If(Product='B','X2', If(WildMatch(Product,'C','D','E'),'X3', If(WildMatch(Product,'F','G','H','I'),'X4', 'XX' )))) as Team
Resident Orig;
DROP Table Orig;
Yes your method is work..
What's more..is there a function in QV that could merge the cells?
Thanks..I just try the metod porvided by @Alexandros17 and I can work..