Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have to transform data from the following Format:
Field | Data |
---|---|
No.: | 1 |
Date: | 28.04.2018 |
User: | User1 |
Action: | Action1 |
No.: | 2 |
Date: | 29.04.2018 |
User: | User2 |
Action: | Action2 |
I have to transform the data to the following Format:
No. | Date | User | Action |
---|---|---|---|
1 | 28.04.2018 | User1 | Action1 |
2 | 29.04.2018 | User2 | Action2 |
Has anyone an idea how to accomplish that?
Best regards
Michael
Do each record has always the same number of rows in the source?
Tomasz
No, sometimes there is an additional line called "checksum". But there is no systematik, where this checksum pops up.
Try this may be
Table:
LOAD *,
PurgeChar(Field, ':') as NewField,
Ceil(RowNo()/4) as RowNum;
LOAD * INLINE [
Field, Data
No.:, 1
Date:, 28.04.2018
User:, User1
Action:, Action1
No.:, 2
Date:, 29.04.2018
User:, User2
Action:, Action2
];
FinalTable:
LOAD Distinct RowNum
Resident Table;
FOR i = 1 to FieldValueCount('NewField')
LET vField = FieldValue('NewField', $(i));
Left Join (FinalTable)
LOAD RowNum,
Data as [$(vField)]
Resident Table
Where NewField = '$(vField)';
NEXT
DROP Table Table;
Are those rows relevant or we can cut those checksums?
Tomasz
I guess this if the number of rows are not the same for each data row
Table:
LOAD *,
PurgeChar(Field, ':') as NewField,
If(Field = 'No.:', Data, Peek('RowNum')) as RowNum;
LOAD * INLINE [
Field, Data
No.:, 1
Date:, 28.04.2018
User:, User1
Action:, Action1
No.:, 2
Date:, 29.04.2018
User:, User2
Action:, Action2
No.:, 3
Date:, 28.04.2019
Action:, Action3
No.:, 4
Date:, 28.04.2020
User:, User4
Action:, Action4
];
FinalTable:
LOAD Distinct RowNum
Resident Table;
FOR i = 1 to FieldValueCount('NewField')
LET vField = FieldValue('NewField', $(i));
Left Join (FinalTable)
LOAD RowNum,
Data as [$(vField)]
Resident Table
Where NewField = '$(vField)';
NEXT
DROP Table Table;
Try Generic load..
T1:
Load
Field,
Data,
If(Field='No',Data,Peek(Key)) as Key
Inline
[
Field,Data
No,1
Date,28.04.2018
User,User1
Action,Action1
No,2
Date,29.04.2018
User,User2
Action,Action2
];
GenericTab:
Generic Load
Key,
Field,
Data
Resident T1
;
Final:
Load Distinct Key
Resident T1;
Drop Table T1;
Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericTab' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo;
For each vTableName in $(vListOfTables)
Left Join (Final) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName;
The checksum is relevant. And there are no headers (Field, Data) available.
The field header (Field, Data) are not available.
The field header (Field, Data) are not available.