QlikView how to reduce loading time with binary .qvw with qualify '*'

[#1]

Hi guys,

I'd wonder if someone has experience with following topic. To this idea brought me this link:QlikView Addict: QlikView Binary Loads

It would be nice to have a simplified loading scenario, where just new data would be actualized and not all the time everything (we need to reload certain documents 5x a day)

I've created a file with just old data OLD.QVW -> this should be reloaded with

binary OLD.QVW

now new data should be loaded

concatenate (TABLE)

load * from NEWTABLE.QVD (QVD) WHERE NOT EXISTS ([%newtablekey]);

This works, however, my issue is with qualified tables before.

First script is made with qualify '*'; unqualify '%*';

Therefore each table column is called TABLE.ROW

My new rows from NEWTABLE.QVD are concatenated into TABLE, but rows are named as TABLE-1.ROW

My question is how to handle this issue? Please don't tell me, don't use unqualify 🙂

Is there any better way to reduce upload time (from data source, 3-tiered, until production in AccessPoint Server)

As always I've attached an example.

Thank you in advance for your hints.

Regards

Stan

[Gysbert_Wassenaar]

If you don't want to unqualify you will have to rename the all fields manually.

load

    TABLE-1.Customer as TABLE.Customer,

    TABLE-1.%LNK as TABLE.%LNK,

    TABLE-1.Value as TABLE.Value

from NEWTABLE.QVD (QVD) WHERE NOT EXISTS ([%newtablekey]);

[#3]

Hi Stanislav,

to avaoid such a behaviour i would recommend you remove your qualify/unqualify on the used table in first old_qvw

You add your new rows on raw (tmp) table and the you qualify/unqualify

then

No concatenate LOAD table from tmp_table.

drop tmp_table

Best regards

Chris

[#4]

Thank you, both of you, guys,

It is not possible to remove qualify from OLD.qvw, because there are certain tables, which do have the same name (but another meaning) -> and shouldn't be renamed.

In my NEW.qvw I would like to actualize more tables, of course, not just one (but many others).

Imangine you have in a data modell orders, deliveries, and bills. Each of them do have more than 10 Mio records.

First of all, it is needed to actualize them more than once a night.

Second old records do not change, but are still needed in the report.

Therefore I would like to improve the loading performance, because it does not make any sence to process with logic records, which have been already processed and nothing changes.

[#5]

Stanislav,

Name your table before qualify

Let say   MyTable  will give in old_qvw MyTable.fieldname

in new qvw  rename your old tables MyTable as OldMyTable

Qualify and load new tables MyTable and concatenate old renamed onesOldMyTable to new ones MyTable

drop renamed table OldMyTable

Chris

[#6]

I'm sorry, Christian, but I didn't get it.

My current scenario: I do have a SAP R3 single table with almost 240 fields/columns and over 10 Million rows. COPA / Controlling

I taught I could improve a performance during uploading process when the most of the records will be as COPA_OLD.QVW which I will load as binary.

So there is no need to rename in my COPA_NEW.QVW old tables. I do just have one big table, where some another are connected to the big one. Let's say one new record has to be appended to the old 10 Million rows.

What should be done and how?

Stan

[#7]

Hi Stanislav

the problem comes either from the QUALIFY either from the structure of your tables.

- When structures are different QlikView gives a name TABLE then TABLE-1. But i don't think this is your pb otherwise you would have a lot of $syn fields.

So let's see the other possibility

- When you use Qualify your fields are stored under TABLE.FieldName.

This is  "Doc_1.qvw"

When you append fields into Doc_2.qvw (using binary  Load Doc_1.qvw)  you have an original TABLE with TABLE.FieldName which is loaded.

You load then (with a QUALIFY *) your new records into TABLE. But if structures are the same, the names are different because first TABLE has TABLE.FieldName and append has FieldName not yet prefixed by TABLE.

So this gives TABLE-1.

To avoid this you must use ALIAS or do the Qualify on the whole table after append.

I hope this is clear. check exemple to understand how it works

Best regards

Chris

[#8]

Honestly, Chris, I'm so sorry. I didn't check your examples.

I understood everything untill your sentece:

"To avoid this you must use ALIAS or do the Qualify on the whole table after append."

do the qualify on the whole table after append ?? could you please explain it to me on my example?

Stan

[#9]

Stanislas,

you should check exemples, it explains your pb with qualify

1. This is what you do

Doc_1.qvw

Qualify *;

unqualify YourKey;

T:

LOAD * From....

gives T.Field_1, T.Field_2...T.Field_N

Doc_2.qvw

Binary Doc_1.qvw  loads table T

Qualify *;

unqualify YourKey;

T:

LOAD * From....(new records)

gives T-1.Field_1, T-1.Field_2...T-1.Field_N

SO TO AVOID THIS, either your replace your QUALIFY *  by alias (Field_1 AS T.Field_1) when you load either you don't qualify table T unless all records are loaded into Doc_2.qvw BECAUSE "Qualify" statement operates after loading

best regards

Chris

Chris

[#10]

Chris, thank you so much.

It is clear NOW after you told me "qualify operates after loading".

Is there any method to rename dynamically all fields as rename all Fields to $(vTable).Field

loop at fields

for each field

rename actual Field TO $(vTable).Field

end for

end loop

??