Data Transformation

[Anonymous]

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

[sasiparupudi1]

Maybe this?

Base:

LOAD SubField(@1,':',1) As Field,

SubField(@1,':',2) As Data

FROM

[298900.txt]

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

T1:

Load

Field,

Data,

If(Field='No',Data,Peek(Key)) as Key

Resident Base;

Drop Table Base;

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;

[Anonymous]

Thank you, that worked!

[sasiparupudi1]

Great, please close the thread by marking a correct and any helpful answers