Create table inside Load Script

[#1]

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

[#2]

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

[#3]

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 · Accepted solution]

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

[#5]

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]

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