Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a good question to ask.
We have data given below in Excel in the particular format. What I need to do is, need to make the rows as a column in the data load editor.
Data:
Code | 1 | 2 | 3 | 4 |
Colour | Red | Green | Yellow | Blue |
Country | DE | UK | FR | DK |
Using the above data I need to transform the data the same as the output table.
Output:
Code | Colour | Country |
1 | Red | DE |
2 | Green | UK |
3 | Yellow | FR |
4 | Blue | DK |
Hi @kartik,
I hope there is a better way, but this is how I did it:
// Start the final table
[Final]:
LOAD * INLINE [
Key
1
2
3
4
];
For Each vType In 'Code','Colour','Country'
[Temp$(vType)]:
Crosstable ([T$(vType)], [$(vType)])
LOAD
[C],
[D],
[E],
[F],
[G]
FROM
[lib://DataFiles/Cross_Table.xlsx]
(ooxml, no labels, table is Tabelle1)
WHERE
Match([C],'$(vType)')
;
LEFT JOIN([Final]) LOAD RecNo() AS [Key], [$(vType)] RESIDENT [Temp$(vType)];
DROP TABLE [Temp$(vType)];
Next vType
DROP FIELD [Key];
Regards,
Mark Costa
Hi @marksouzacosta ,
Thanks, this worked but I think there should be some easy way too for this....
Can't we do this with Crosstable or generic load ?
Crosstable and Generic Loads were my first attempts but I had no success with them. I don't discard the possibilities of better and more straightforward solutions. I'm just not seeing it yet.
Regards,
Mark Costa
Hi, if excel is the source you can add tranformations while loading the data, like:
LOAD Code,
Colour,
Country
FROM
[lib://DataFiles/Cross_Table.xlsx]
(ooxml, embedded labels, table is Tabelle1, filters(
Remove(Row, RowCnd(CellValue, 1, StrCnd(shorter, 0))),
Remove(Col, Pos(Top, 1)),
Transpose()
));
Hi @kartik.,
Check this one:
[Your_CrosstableTable]:
Crosstable (Code, Value, 1)
LOAD * INLINE [
Attribute, 1, 2, 3, 4
Colour, Red, Green, Yellow, Blue
Country, DE, UK, FR, DK
];
FinalTable:
LOAD
Code as Code,
If(Attribute = 'Colour', Value) as Colour
Resident [Your_CrosstableTable]
Where Attribute = 'Colour' ;
Left JOIN (FinalTable)
LOAD
Code as Code,
If(Attribute = 'Country', Value) as Country
Resident [Your_CrosstableTable]
Where Attribute = 'Country';
DROP TABLE [Your_CrosstableTable];
***Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.***