Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

possible to reuse list of fields (and their renames) for repeated use in Load Script?

Hello! I have a load script that consumes monthly files from an external source having hundreds of static field names and millions of records. To streamline, I only load the minimal set of fields I actually need. I loop through each of the monthly files and concatenate into my existing table of all months.

Note that each monthly file is regenerated every month, so I can't just do an incremental load on the most recent month.

I use a switch statement on a boolean variable to determine whether I'm loading the first month of data into a new combined months table or if I'm loading subsequent months into the exiting combined months table. The switch statement on the boolean variable determines how to set the concatenation.

I mocked up the code below using two fields and their renamed field names to simplify. Right now, I have to maintain identical lists of fields and their renamed values in the TRUE and FALSE blocks within the switch.

Is there any way to push this field list outside of the Switch and just refer to it? This would cut down on manual maintenance and resulting errors of trying to maintain the two lists of fields/aliases in parallel within the switch block.

Let vHasRunFirst = FALSE();

FOR EACH SourceFile IN FILELIST('$(cGenericFileString)')

     SWITCH vHasRunFirst

          CASE TRUE()

               CONCATENATE ($(tCombinedMonths))

               LOAD

                    ExternalField1 AS 'InternalField1',

                    ExternalField2 AS 'InternalField2'

               FROM [$(SourceFile)] (QVD)

               ;

          CASE FALSE()

               $(tCombinedMonths😞

               NOCONCATENATE

               LOAD

                    ExternalField1 AS 'InternalField1',

                    ExternalField2 AS 'InternalField2'

               FROM [$(SourceFile)] (QVD)

               ;

               Let vHasRunFirst = TRUE();

     END SWITCH

NEXT SourceFile

I tried googling this and searching the existing QV community forum posts, but I don't see anyone else asking this.

Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Instead of repeating the LOAD blocks, you can just switch the CONCATENATE prefix:

Set vConcatenate = ;

For each vFile in FileList('.\*.txt')

Data:

$(vConcatenate)

LOAD Field1 as FIELD1,

          Field2 as FIELD2

FROM [$(vFile)];

Set vConcatenate = Concatenate ;

Next vFile

View solution in original post

5 Replies
swuehl
MVP
MVP

Instead of repeating the LOAD blocks, you can just switch the CONCATENATE prefix:

Set vConcatenate = ;

For each vFile in FileList('.\*.txt')

Data:

$(vConcatenate)

LOAD Field1 as FIELD1,

          Field2 as FIELD2

FROM [$(vFile)];

Set vConcatenate = Concatenate ;

Next vFile

Not applicable
Author

Thank you for the quick response.

When I try to do this, syntax checking in the script editor gets angry. It underlines in red the table name, the concatenation variable, and the 'LOAD' piece of the load statement. This happens whether my table name is hard-coded or a variable. If I change the variable $(vConcatenate) to hard-coded "CONCATENATE", then script error checking says everything is ok.

Am I missing something?

rbecher
MVP
MVP

Hi,

you could always load into a temp table and use "Rename Fields Using <Map>" with a preloaded Mapping Table containing the field names. You also could create the whole LOAD statement using $-expansion driven by an external file/table.

- Ralf

Astrato.io Head of R&D
swuehl
MVP
MVP

Have you tried executing the script?

Not applicable
Author

I tried different permutations to make the script syntax checking go away. I never succeed. Then I just ran the script despite the red, highlighted "errors".

It worked. You were right. I guess the syntax checker isn't infallible.

Thank you very much for your help!