Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create table inside Load Script

Hi all,

I'm noticing a behavior I cannot explain by myself. Inside my Load script I create a table on which I add a row each time a condition is matched. In particular I have two nested FOR and a IF statement inside them:

FOR i = 0 TO NoOfRows('G_L Account FINE') - 1

    LET vTableLtot = peek('G_L Account FINE.LTot', $(i), 'G_L Account FINE');

    LET vTableRtot = peek('G_L Account FINE.RTot', $(i), 'G_L Account FINE');

    LET vTableInd = peek('G_L Account FINE.Indentation', $(i), 'G_L Account FINE');

    LET vTableTot = peek('G_L Account FINE.Totaling', $(i), 'G_L Account FINE');

   

    FOR j = 0 TO NoOfRows('G_L Account') - 1

   

            LET vTableNo = peek('K_No_GLAccount', $(j), 'G_L Account');

           

            IF $(vTableNo) >= $(vTableLtot) AND $(vTableNo) <= $(vTableRtot) THEN

           

                QUALIFY *;

                UNQUALIFY '*';

                [tmp_table]:

                concatenate

                load

                    '$(vTableNo)' as K_No_GLAccount,

                    '$(vTableInd)' as Indentation,

                    '$(vTableTot)' as Totaling

                autogenerate(1)

                ;

                //drop Table tmp_table;

            ELSE

            ENDIF

           

    NEXT j

NEXT i

;

drop Table [tmp_table];


I'd like to handle my table outside the nested for loop, but I can't. In the example above I try to drop the table just outside the end of the FOR statement. When I reload data an error is raised saying that the table does not exist. I receive the same error if I try to uncomment the Drop Tabe statement inside the IF caluse.

Furthermore I can show tmp_table values inside my dashbord but I see no trace of the table inside Table Viewer.

What is happening? can someone, please, explain me whant I'm actually doing wrong?


Thanks in advance

Andrea

1 Solution

Accepted Solutions
rwunderlich
Luminary Alumni
Luminary Alumni

If the fields are there, they are being place in some table. The Concatenate keyword is causing it to be concatenated with the previously created table in the script, not the tmp_table. It looks to me like you can remove the Concatenate keyword in this case. 

Create listboxes for $Field and $Table. Click on the fieldname to find out what table it's in.

-Rob

http://masterssummit.com

View solution in original post

5 Replies
Not applicable
Author

Andrea

if condition is not filled, table is not created so you can't drop it

Remark : ELSE is not mandatory if you don't have any instructions

Chris

Not applicable
Author

Hi Christian,

thank you for your fast reply. Sometimes (7000 times, actually) condition is filled, so I have 7000 rows in my table. Table exists, so I suppose to be able to drop it, right?

Thanks for your remark, I forgot to remove it.

Regards

rwunderlich
Luminary Alumni
Luminary Alumni

If the fields are there, they are being place in some table. The Concatenate keyword is causing it to be concatenated with the previously created table in the script, not the tmp_table. It looks to me like you can remove the Concatenate keyword in this case. 

Create listboxes for $Field and $Table. Click on the fieldname to find out what table it's in.

-Rob

http://masterssummit.com

View solution in original post

Not applicable
Author

Thanks Rob,

it's a pleasure to have an answer by you. It's correct, what was disorienting to me was finding tmpTable.Indentation, for example, within the available fields. This is actually the unqualified name, and not the fully qualified one.

Anyway I still need the Concatenate statement in order not to recreate a copy of the table on each FOR-cycle execution (I see messages like tmp_table-1234 << AUTOGENERATE(1) when reloading data ad the whole load process is very slow). I'll try to declare a table just before the first FOR cycle in order to concatenate the rows to this table, maybe I'll fill it with a fake row (I think I cannot declare a table name without inserting any data, right?)

thanks

Andrea

rwunderlich
Luminary Alumni
Luminary Alumni

Yes, you can create a dummy table prior to the concat like:

tmp_table:

LOAD 1 as Dummy autogenerate 1;

...then do your loop

DROP FIELD Dummy;

-Rob