Solved: subfield() - it's fine, but it's TOO SLOWLY!!! - Qlik Community

Report Inappropriate Content · ‎2014-08-16

Hello

I have table with 2 columns: ContractNumber and VALUE

Field VALUE like this "11231,432423,43545,565465,456456"

ContractNumber like this 2432423/3234

I need to separate values in VALUE, when I try use subfield() - it's fine, but it's TOO SLOWLY!!! Then I try to use load with separator ',' - it's great, it's works fast, but I can't save ContractNumber in this case ;(((

I need help ... please

rbecher · ‎2014-08-17

Hi Gertye,

you could replace the delimiter with a line break, store it in a file and read again:

ContractByLinksTmp:

LOAD RecNo() as ContractNumber, replace(VALUE, ',', chr(13) & chr(10) & RecNo() & chr(9)) as VALUE

FROM

[ContractByLinks.txt]

(txt, utf8, embedded labels, delimiter is '\t', msq);

STORE ContractByLinksTmp INTO ContractByLinksTmp.txt (txt, delimiter is '\t');

Drop Table ContractByLinksTmp;

LOAD ContractNumber, replace(VALUE, '"', '') as VALUE

FROM

(txt, utf8, embedded labels, delimiter is '\t', no quotes);

STORE ContractByLinksTmp INTO ContractByLinksOut.txt (txt, delimiter is '\t');

-----------------

I used RecNo to simulate the ContractNumber (missing in file).

- Ralf

Astrato.io Head of R&D

View solution in original post

ecolomer · ‎2014-08-16

can you try with left and right funtions

Report Inappropriate Content · ‎2014-08-16

How?

Here is I load separated values of VALUE:

LOAD

@1 as value

FROM

ContractByLinks.txt

(txt, no labels, delimiter is ',', no quotes);

join by which field? with... what?

ecolomer · ‎2014-08-16

Send my a file to try

Report Inappropriate Content · ‎2014-08-16

thanks for try

jagan · ‎2014-08-16

Hi,

I think SubField() is the right option in this case eventhough it is slow. How many records you have in the table? If you have more records then it will definitely take time to load, but you will correct correct data.

Regards,

Jagan.

ecolomer · ‎2014-08-16

I agree with Jagan, Subfield es the best solution

Report Inappropriate Content · ‎2014-08-16

I have more than 30 000 000 records . When I try to load data using subfield() - I think the load will never finish, it's works more then 24 hours. I really can't use SubField() function.

Second problem: I need to reload my data every day, so I can't take more than 24 hours for my script.

Report Inappropriate Content · ‎2014-08-16

Not for me

ecolomer · ‎2014-08-16

This 30M records you read all days?

And this recordas are diferente each day?

if NO, other solution is to save the records converted in a QVD file .... you know it?