Discussion Board for collaboration related to QlikView App Development.
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
now new data should be loaded
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.
If you don't want to unqualify you will have to rename the all fields manually.
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]);
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
No concatenate LOAD table from tmp_table.
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.
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
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?
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
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?
you should check exemples, it explains your pb with qualify
1. This is what you do
LOAD * From....
gives T.Field_1, T.Field_2...T.Field_N
Binary Doc_1.qvw loads table 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
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