Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi guys any sugg. it looks little tricky?
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.
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
hi marcus,
thanks.. what adjustments needs to be done. basically i changed the file name and ran the script and the output looks complicated..
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
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
Here a small adjustment:
- Marcus
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
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
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