Transform Data

zwyne — Wed, 25 Nov 2020 16:16:04 GMT

Hi Rob,

My thread expired late last night. But just to follow up my question, the data is shown as sample in one column per row.

Thanks

Re: Transform Data

pradosh_thakur — Tue, 06 Feb 2018 07:15:49 GMT

use full read

The Magic of Dollar Expansions

Use cases for Generic Load | Qlikview Cookbook‌

try this

Table:

GENERIC LOAD 1 AS DUMMY,

PICK(MATCH(1,SUBSTRINGCOUNT(FIELD1,'Account number'),SUBSTRINGCOUNT(FIELD1,'Date'),SUBSTRINGCOUNT(FIELD1,'Method of transaction'),SUBSTRINGCOUNT(FIELD1,'Amount')),

LEFT(FIELD1,14),LEFT(FIELD1,4),LEFT(FIELD1,21),LEFT(FIELD1,6)) AS Column1,

PICK(MATCH(1,SUBSTRINGCOUNT(FIELD1,'Account number'),SUBSTRINGCOUNT(FIELD1,'Date'),SUBSTRINGCOUNT(FIELD1,'Method of transaction'),SUBSTRINGCOUNT(FIELD1,'Amount')),

Mid(FIELD1,16),MID(FIELD1,6),MID(FIELD1,23),MID(FIELD1,9)) AS Column2,

;

LOAD * INLINE [

FIELD1

Account number: 122225

Date: 09/01/2018

Method of transaction: XXX Credit

"Amount : 233610,97"

Account number: 554555

Date: 02/11/2017

Method of transaction: XXX Credit

"Amount: 155403,09"

Account number: 678919

Date: 16/10/2017

Method of transaction: XXX Credit

"Amount: 151698,36"

];

Set vjoin = ;

FOR i = NoOfTables()-1 to 0 STEP -1

LET vTable=TableName($(i));

IF WildMatch('$(vTable)', 'Table.*') THEN

abc:

$(vjoin)

LOAD *,rowno() as r1 RESIDENT [$(vTable)];

Set vjoin = left join ;

DROP TABLE [$(vTable)];

ENDIF

NEXT i

drop Fields DUMMY,r1;

topic Transform Data in QlikView

Transform Data

Re: Transform Data