exclude tables from storing as qvds in loop

[ioannaiogr]

Hello everyone.

In the process of storing qvds, I want to exclude the tables called 'TABLE1' and 'TABLE2', because I have previously stored them into another Path.

So in this step, I was thinking of excluding them like that with an If

For i=0 to NoOfTables()-1

let d=TableName(i);

If $(d)<>'TABLE1' or $(d)<>'TABLE2' then
Store [$(d)] into [$(vPathQVD)/$(d)].qvd;

end if;
Next;

but the exception is not working well. Any ideas that you maybe have? Thank you in advance

[marcus_sommer]

It depends of the context respectively where you call a variable in which syntax they needs to be specified.

Within the store-statement a string is expected and therefore you could use [$(d)] but within the if-loop various content is possible and therefore the variable must be wrapped with single-quotes to force a string-evaluation, means something like: if '$(d)' = 'Table1' ...

- Marcus

[ioannaiogr]

Hi @marcus_sommer , have a good week!

Well, tried it in if statement like '$(d)' and $(d), still gets stored in both paths , so the exception doesn't work.

Should i change something in the code that follows for qvd creation?

That's the rest of the code i'm using after this previous bit

Let vLoadComplete = Now();

// Loop through the tables within the model
FOR i = 0 TO noOfTables()-1 STEP 1
LET vCurrTable = tablename(0); // Get the current table name
DROP Table [$(vCurrTable)];
NEXT;

// Clear the variables so they don't persist
LET vCurrTable = null();

[marcus_sommer]

Yes, the dropping-loop may cause your issue because it impact the table-index within the data-model.

To prevent this you may run the loop reverse and include store + drop within a single loop or fetching at first all table-names within an array-variable/table and then running through it. The first seems simpler with:

for i = nooftables() -1 to 0 step -1
...
next

Beside this I suggest to check the entire approach in regard to performance + RAM consumption + possible influencing of the multiple table-load to each other. This means it's often better and/or necessary to store and drop the tables immediately after the load/transforming and not at the end of the script.

- Marcus

[ioannaiogr]

-@marcus_sommer  :

The loop in reverse did not help either. I'm most probably doing something wrong though, or missing out on something. 

I tried setting a path, storing and immediately dropping the qvds after storing them in this certain path. Then I set a second path , picked out the batch of qvds i want to store in this path, then immediately dropped them after storing each. All this instead of doing it all at the end as you suggested. This seems to work.

(The reason why i am considering the above is because for the ones in the second path i needed incremental load which i can't do, so at every run i made a routine to truncate the content of them then rebuild them. Can't think of anything else at this moment for that , or better, i don't have the knowledge to implement something else🤔)

[marcus_sommer]

If loops or other things didn't work like expected I add quite often some TRACE statements to track the essential parts of the execution and/or I create a special kind of log-table, for example something like:

....
Log:
load $(i) as LoopCounter, '$(TableName)' as TableName, nooftables() as NoOdTables,
           'Store' as Action, StorePath, ...
autogenerate 1;
....

Log:
load $(i) as LoopCounter, '$(TableName)' as TableName, nooftables() as NoOdTables,
           'Delete' as Action, StorePath, ...
autogenerate 1;

With it you could track all actions with all relevant parameters within the executed order and see if all values are like expected and/or if any actions/tables are missing or executed twice or ... and usually it becomes quite obvious what goes wrong.

- Marcus