Transform Data

[zwyne]

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

[pradosh_thakur · Accepted solution]

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;