Loop within the load statement

[vlad_komarov]

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

[petter]

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

[vlad_komarov]

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

[rwunderlich]

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