topic Re: text in QlikView

text

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

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

Re: text

beck_bakytbek — Thu, 20 Apr 2017 08:17:23 GMT

not clear what do you mean?

Can you give a screenshot?

Beck

Re: text

raadwiptec — Thu, 20 Apr 2017 08:26:14 GMT

My source file is a text file and it looks as below..

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

I need the output as below

Name | Adres | IBAN

BNP UK UKXXXXX

BNP US USXXXXXX

Re: text

beck_bakytbek — Thu, 20 Apr 2017 08:33:36 GMT

Try this:

load

Name,

Land rekening as Adres,

Left(IBAN rekeningnummerLokaal,5) as IBAN_NR

Re: text

raadwiptec — Thu, 20 Apr 2017 09:04:14 GMT

but for IBAN rekeningnummerLokaal,? its not a column the data is one below the other

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

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

Re: text

beck_bakytbek — Thu, 20 Apr 2017 09:10:13 GMT

is "IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank" Name of Field?

Re: text

tamilarasu — Thu, 20 Apr 2017 09:37:40 GMT

Could you attach your text file here?

Re: text

raadwiptec — Thu, 20 Apr 2017 11:09:49 GMT

is it the name of the field but it just in a text file ,theree are no seperators etc.

Re: text

beck_bakytbek — Thu, 20 Apr 2017 11:22:58 GMT

Look at this function:

https://www.youtube.com/watch?v=m5h70rj629g

Re: text

raadwiptec — Thu, 20 Apr 2017 11:52:05 GMT

Hi tamil..

attached..

Re: text

raadwiptec — Sun, 23 Apr 2017 17:42:45 GMT

hi guys any sugg. it looks little tricky?

Re: text

tamilarasu — Sun, 23 Apr 2017 17:57:21 GMT

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.

Re: text

marcus_sommer — Mon, 24 Apr 2017 11:29:25 GMT

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

Re: text

raadwiptec — Wed, 26 Apr 2017 13:46:55 GMT

hi marcus,

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

Re: text

marcus_sommer — Wed, 26 Apr 2017 14:30:57 GMT

For me it's not clear if:

IBAN rekeningnummerLokaal rekeningnummerVrijBICNaam bankAdres bank

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

Re: text

raadwiptec — Wed, 26 Apr 2017 16:09:40 GMT

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

Re: text

marcus_sommer — Thu, 27 Apr 2017 09:00:49 GMT

Here a small adjustment:

- Marcus

Re: text

raadwiptec — Thu, 27 Apr 2017 14:28:15 GMT

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

Re: text

marcus_sommer — Thu, 27 Apr 2017 18:09:31 GMT

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:

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);

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;

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

- Marcus

Re: text

raadwiptec — Tue, 02 May 2017 10:01:13 GMT

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