Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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!

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

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

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

5 Replies
MVP
MVP

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

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

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

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?

MVP & Luminary
MVP & Luminary

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

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

MVP
MVP

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

Have you tried executing the script?

Not applicable

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

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!