topic Re: Data Transformation in QlikView

Data Transformation

Anonymous — Thu, 19 Apr 2018 13:17:08 GMT

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

Re: Data Transformation

tomasz_tru — Thu, 19 Apr 2018 14:33:24 GMT

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

Tomasz

Re: Data Transformation

Anonymous — Thu, 19 Apr 2018 14:40:57 GMT

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

Re: Data Transformation

sunny_talwar — Thu, 19 Apr 2018 14:42:10 GMT

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;

Re: Data Transformation

tomasz_tru — Thu, 19 Apr 2018 14:50:33 GMT

Are those rows relevant or we can cut those checksums?

Tomasz

Re: Data Transformation

sunny_talwar — Thu, 19 Apr 2018 14:55:36 GMT

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;

Re: Data Transformation

sasiparupudi1 — Thu, 19 Apr 2018 17:41:39 GMT

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;

Re: Data Transformation

Anonymous — Fri, 20 Apr 2018 05:46:13 GMT

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

Re: Data Transformation

Anonymous — Fri, 20 Apr 2018 05:46:55 GMT

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

Re: Data Transformation

Anonymous — Fri, 20 Apr 2018 05:47:01 GMT

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

Re: Data Transformation

sasiparupudi1 — Fri, 20 Apr 2018 06:33:49 GMT

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;

Re: Data Transformation

Anonymous — Fri, 20 Apr 2018 06:52:58 GMT

Thank you, that worked!

Re: Data Transformation

sasiparupudi1 — Fri, 20 Apr 2018 06:55:33 GMT

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