topic Re: QlikView how to reduce loading time with binary .qvw with qualify '*' in QlikView

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

Fri, 25 Oct 2013 08:44:06 GMT

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

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

Gysbert_Wassenaar — Fri, 25 Oct 2013 09:03:34 GMT

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]);

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

Fri, 25 Oct 2013 09:08:27 GMT

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

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

Fri, 25 Oct 2013 09:16:39 GMT

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.

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

Fri, 25 Oct 2013 09:29:26 GMT

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

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

Mon, 28 Oct 2013 12:13:32 GMT

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

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

Mon, 28 Oct 2013 12:57:15 GMT

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

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

Mon, 28 Oct 2013 13:05:55 GMT

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

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

Mon, 28 Oct 2013 13:35:37 GMT

Stanislas,

you should check exemples, it explains your pb with qualify

1. This is what you do

Doc_1.qvw

Qualify *;

unqualify YourKey;

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;

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

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

Mon, 28 Oct 2013 13:52:37 GMT

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

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

Mon, 28 Oct 2013 14:04:09 GMT

Stan

if you do need table T in both qvw i would do one T qualified for doc_1 and one T not qualified for doc_2

Doc_1

Tmp:

LOAD * ....

Qualify *

noconcatenate

LOAD * resident Tmp

Doc_2

Tmp:

LOAD * from new records

Drop table T;

qualify *

LOAD * resident Tmp

drop talbe Tmp;

You'll get talbe T in doc_1 and in doc_2 with new records

best regards

Chris

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

Mon, 28 Oct 2013 14:36:57 GMT

Chris,

my approach is a little bit different. I would like to do the best twice binary .qvw (but this is not possible at the moment).

Therefore I've created a script where new data should be appended to old ones. First table Table1 would come from binary.qvw, tmp should new records. Is it possible to change the column names in tmp dynamically without making a copy? This woun't work because there is already a column named Table1.Feld1 ...

Table1 = old data

tmp = new data

qualify *;
Table1:
load * inline

[Feld1, Feld2
1,2
3,4
5,6
7,8];

unqualify *;
//concatenate
noConcatenate
tmp:
load * inline
[Feld1, Feld2
25,60
27,80
28,90
29,100
30,110];

For f = 1 to NoOfFields('tmp')
    let vFieldOld = FieldName($(f),'tmp');
    let vFieldNew = 'Table1.$(vFieldOld)';
    RENAME FIELD $(vFieldOld) to $(vFieldNew);
Next f;

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

Mon, 28 Oct 2013 14:55:56 GMT

Sorry Stan,

I've no idea for that.

From my point of view, the simpliest is the best.

Good luck

Chris