topic Re: Loop within the load statement in QlikView

Loop within the load statement

vlad_komarov — Fri, 12 Jun 2015 18:08:24 GMT

Hello!

I have a data table that contains strings loaded from TXT file. These strings are actually variables' definitions:

let vTaskAssignedToRegion= '=GetFieldSelections(TASK_ASSIGNED_TO_REGION)';

let vStartCount = '=sum({<SessionDateNum={">$(=Num(Today()-90))"}, User-={"Anonymous"}>} Session_Start_Count)';

Loading these strings produces one challenge: some variables are defined on multiple lines in the input file:

let vStartCount = '=sum(

{<SessionDateNum={">$(=Num(Today()-90))"},

User-={"Anonymous"}>}

Session_Start_Count)';

I was thinking about loading strings between "let" and ";" sub-strings and concatenate them into one result record.

What's the most efficient way to do that?

The only way I can see now is to organize a loop within the load script and concatenate strings from "let" line until the ";" character is reached in sequential strings load.

I am not sure if it's possible, first of all, and it does not look like a most efficient way to do it anyway.

Any suggestions?

Best regards,

Vladimir

Re: Loop within the load statement

petter — Fri, 12 Jun 2015 18:24:04 GMT

You could convince QlikView that CR + LF is not end of record by telling it it that the record is actually 1024 lines long or whichever max size that is higher than you will ever expect in your input file. Then QlikView happily loads the entire text file into one single string. Then you could break it down into lines by semicolons instead by using SubField().

It is as simple as this:

LOAD

SubField( [@1:n] , ';' ) AS Statements

FROM

myTextFile.txt

(fix, codepage is 1252, record is 1024 lines);

Re: Loop within the load statement

vlad_komarov — Fri, 12 Jun 2015 18:51:44 GMT

Thanks, Peter!

This approach gives me some flexibility, but adds some extra challenges (filtering commented lines, extra lines between variables, etc). But it looks like a path to the right direction. Will try to adjust it to my scenario.

Thanks!

Regards,

Vladimir

Re: Loop within the load statement

rwunderlich — Fri, 12 Jun 2015 19:53:08 GMT

I think you can read them as regular lines, concat into a single line and then subfield by ';'. You might want to make your subfield delim ';' & chr(10) to avoid mistaking embedded ';' as the end of the statement.

LOAD

subfield(AllStatements, ';') as Statement

;

LOAD

concat(Statements,'',RecNo()) as AllStatements

;

LOAD Statements

FROM

myTextFile.txt .....

-Rob

http://masterssummit.com

http://robwunderlich.com