text

[raadwiptec]

I have the following below source text file similiarly for many parts until part 25 clients .. XXX are bank account numbers so put it as XXX

I want to make as below (Basically to transpose

Name | Adres | IBAN

BNP     UK        UKXXXXX

Below source files

part1

Name :BNP

Adres : "

UK"

Alias :bnp bank

Land rekening :UK

Telefoonnummer:

E-mail:

Gedeeld : Nee

Rekeningen

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX           Country

part2

Naam :BNP

Adres : "

US"

Alias :bnp bank

Land rekening :UK

Telefoonnummer:

E-mail:

Gedeeld : Nee

Rekeningen

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX           Country

[raadwiptec]

hi guys any sugg. it looks little tricky?

[tamilarasu]

Hi Raadwiptec,

Yes Indeed. I tried to load the text file with different method but no luck. I was busy on that day so couldn't get time to check it again. I will try again and let you know If I get the required output. I always ask marcus_sommerfor my doubts. Lets wait for his reply.

[marcus_sommer]

It's not very nice and I think you will need some adjustments and a bit fine-tuning to catch all exceptions in the right way but I believe it shouldn't be too difficult on the following basis:

t0:

LOAD

    replace(replace(replace(replace(@1, ' :', ':'), 'IBAN', 'IBAN:'), 'Name', 'Naam'), 'Alias', chr(9) & 'Alias') as Data,

    if(rowno() <= 2, 1, if(left(@1,4) = 'part', peek('ID') + 1, peek('ID'))) as ID,

    recno() as RecNo

FROM [qlik.txt] (txt, codepage is 1252, no labels, delimiter is \t);

t1:

Load ID, concat(Data, chr(9), RecNo) as Data

resident t0 where len(trim(Data)) > 0 and left(Data, 4) <> 'part' group by ID;

t2:

load ID, subfield(Data, ':', 1) as Field, subfield(Data, ':', 2) as Value, Data, rowno() as RowNo;

load ID, subfield(replace(Data, '"' & chr(9), '"'), chr(9)) as Data

resident t1;

t3:

generic load ID, Field, Value resident t2;

t4:

load distinct ID resident t2;

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

  LET vTable=TableName($(i));

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

  LEFT JOIN ([t4]) LOAD distinct * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

drop tables t0, t1, t2;

- Marcus

[raadwiptec]

hi marcus,

thanks.. what adjustments needs to be done. basically i changed the file name and ran the script and the output looks complicated..

[marcus_sommer]

For me it's not clear if:

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

or

UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX

is a single field-value or are several concatenated values - if they are several (and needed) values you will need to extend the above used logic to split them - maybe by further subfield-loop or if you could define certain structures you might cut them with various string-functions.

This isn't an easy job especially because there will be quite probably some more differences within the data-structures in your origin data which needs more replace-statements and/or if-loops to catch them all.

After all I think the best will be to try to to get a reliable data-source for this maybe by a direct access to the database as to play with these unstructured (and ugly) data (for quite a long time).

- Marcus

[raadwiptec]

Hi Marcus,

The Structure is exactly the same as below - As all these are bank account numbers i have maintained as XXX.so here iam showing only part 1.. but similarly i have part 2 ...part 33..until part 75 something but with the same below structure ,different bank numbers and different account numbers. all these are in one text file

part1

Name :BNP

Adres : "

UK"

Alias :bnp bank

Land rekening :UK

Telefoonnummer:

E-mail:

Gedeeld : Nee

Rekeningen

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX           Country

so here if you see

Name :BNP.  -here name is column and BNP is the value

Adres : "         same as above

Alias :bnp bank

E-mail:

Gedeeld : Nee--

all the above has a seperator

but  for IBAN the heading is above..and does not have a seperator. and the account number is below..so IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank is basically the column name and

UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX  is the value for the IBAN

and yes it is a single field

So the output should be like below

Name | Alias |   Adress  | Gedeeld | IBAN (either IBAN or the full text)

BNP     bnp bank  blank    Nee      UKXX 0000 XXX XXXXXX-XXXXXX-XXXXXXBNP PARIBAS FORTIS (FORTIS BANK SA/NV)XXXX

[marcus_sommer]

Here a small adjustment:

- Marcus

[raadwiptec]

Hi marcus, thanks with small adjustment here also iam able to achieve the results ..but i tried only for few lines from part 1 to part 5. I have approx 7000 lines in the text files totally. which makes the code to get stuck for hours..anyway to index it or make it easier

[marcus_sommer]

I could imagine that a split of data to maybe 10 parts and than a loop over them would speed up the script. I mean generating a qvd from data with a loop-counter - maybe in this way:

a:

LOAD

    replace(replace(replace(replace(@1, ' :', ':'), 'IBAN', 'IBAN:'), 'Name', 'Naam'), 'Alias', chr(9) & 'Alias') as Data,

    if(rowno() <= 2, 1, if(left(@1,4) = 'part', peek('ID') + 1, peek('ID'))) as ID,

    recno() as RecNo

FROM [qlik.txt] (txt, codepage is 1252, no labels, delimiter is \t);

b:

load *, ceil(ID / 10) as LoopCounter resident a;

store b into b.qvd (qvd);

let vLoopCounter = fieldvaluecount('LoopCounter');

drop tables b;

for i = 1 to vLoopCounter

     let vLoopValue = $(i) * 10;

     t1:

     Load ID, concat(Data, chr(9), RecNo) as Data

     from b.qvd (qvd)

      where len(trim(Data)) > 0 and left(Data, 4) <> 'part' and

          LoopCounter = $(vLoopValue)

     group by ID;

     // ... the other load statements

     store t4 into t4_$(vLoopValue).qvd (qvd);

     drop tables t4;

next

And after that you load all t4_$(vLoopValue).qvd's again.

- Marcus

[raadwiptec]

hi marcus,

Sorry your script is goofd working.. but their is one change for the new files ,we do not have part 1 ,part2 ..i.e nstead of part1 and part 2 ,We have only Tegenpartij and following this making an ID column  is an issue and further duplication happens

Tegenpartij

Naam :Bank1

Adres : "MOLENVEST 25

2200 HERENTALS

BELGIE"

Alias :DHAXLEY

Land rekening :BELGIE

Telefoonnummer:

E-mail:

Gedeeld : Nee

Rekeningen

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

XXXXXXXXXXXXXXXX

Tegenpartij

Naam :Bank2

Adres : "VIA EL FORTE 10

6900 LUGANO

ZWITSERLAND"

Alias :AMICONI CONSULTING SA

Land rekening :ZWITSERLAND

Telefoonnummer:

E-mail:

Gedeeld : Nee

Rekeningen

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

xxxxxxxxxxxxxxxxxxx