Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
As we know the generic prefix will generate three table .
However is it possible to join three table into one single table by key.
I have given example
Table1:
Generic LOAD * INLINE;
[
Key, Attribute, Value
Bob, Jan, 100
Bob, Feb, 200
Bob, Mar, 300
Kate, Jan, 400
Kate, Feb, 500
Kate, Mar, 600
];
Result:
This will result in 3 tables:
Table1.Jan:
Key, | Jan |
Bob, | 100 |
Kate, | 400 |
Table1.Feb:
Key, | Feb |
Bob, | 200 |
Kate, | 500 |
Table1.Mar:
Key, | Mar |
Bob, | 300 |
Kate, | 600 |
Here is another way to do this without using Generic prefix
Table1:
LOAD * INLINE [
Key, Attribute, Value
Bob, Jan, 100
Bob, Feb, 200
Bob, Mar, 300
Kate, Jan, 400
Kate, Feb, 500
Kate, Mar, 600
];
FinalTable:
LOAD DISTINCT Key
Resident Table1;
FOR i = 1 to FieldValueCount('Attribute')
LET vAttribute = FieldValue('Attribute', $(i));
Left Join (FinalTable)
LOAD Key,
Value as $(vAttribute)
Resident Table1
Where Attribute = '$(vAttribute)';
NEXT i
DROP Table Table1;
This link provides a method:
Here is another way to do this without using Generic prefix
Table1:
LOAD * INLINE [
Key, Attribute, Value
Bob, Jan, 100
Bob, Feb, 200
Bob, Mar, 300
Kate, Jan, 400
Kate, Feb, 500
Kate, Mar, 600
];
FinalTable:
LOAD DISTINCT Key
Resident Table1;
FOR i = 1 to FieldValueCount('Attribute')
LET vAttribute = FieldValue('Attribute', $(i));
Left Join (FinalTable)
LOAD Key,
Value as $(vAttribute)
Resident Table1
Where Attribute = '$(vAttribute)';
NEXT i
DROP Table Table1;