Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Data Transformation

Hi,

I have to transform data from the following Format:

FieldData
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.DateUserAction
128.04.2018User1Action1
229.04.2018User2

Action2

Has anyone an idea how to accomplish that?

Best regards

Michael

12 Replies
tomasz_tru
Specialist
Specialist

Do each record has always the same number of rows in the source?

Tomasz

Anonymous
Not applicable
Author

No, sometimes there is an additional line called "checksum". But there is no systematik, where this checksum pops up.

sunny_talwar

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;

tomasz_tru
Specialist
Specialist

Are those rows relevant or we can cut those checksums?

Tomasz

sunny_talwar

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;

Capture.PNG

sasiparupudi1
Master III
Master III

Try Generic load..

19-04-2018 12-33-28.png

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;

Anonymous
Not applicable
Author

The checksum is relevant. And there are no headers (Field, Data) available.

Anonymous
Not applicable
Author

The field header (Field, Data) are not available.

Anonymous
Not applicable
Author

The field header (Field, Data) are not available.