Partial reload and Temp table

[lematiethibault]

Dear all,

I've got a problem with the partial reload and I'm really stucked.

I've got a big application qlikview and I would like to reload it only for a domain (Customer base or Sale or ....) without loosing what I got in memory. Using a variable for each domain I'm pretty sure that I can do it.

So I've tried to test the partial reload solution, unfortunatelly the result is not what I expected so I would really appreciate your help.

Here is my test case, I've tried to make it as simple as possible :

So let's make a first Load with the following script

testtmp:

Replace

LOAD * INLINE [

    F1, F2

    1, a

    2, b

];

NoConcatenate

test2:

Replace

Load *

          ,F1*2 as test

          ,F1*3 as test2

Resident testtmp;

drop table testtmp;

test3:

LOAD * INLINE [

    E1, E2

    1, a

    2, b

];

NoConcatenate

test4:

Load * Resident test3;

drop table test3;

Without suprise, the result will be

test2

F1        F2     test     test2

1         a        2         3

2         b        4         6

test4

E1      E2

1         a

2         b

Then let's modify the inline value and then partial reload it:

testtmp:

Replace

LOAD * INLINE [

    F1, F2

    3, a

    4, b

];

NoConcatenate

test2:

Replace

Load *

          ,F1*2 as test

          ,F1*3 as test2

Resident testtmp;

drop table testtmp;

test3:

LOAD * INLINE [

    E1, E2

    3, a

    4, b

];

NoConcatenate

test4:

Load * Resident test3;

drop table test3;

Surprisingly, the result won't be as exepected :

test2

F1        F2     test     test2

3         a        6         9

4         b        8         12

test4

E1      E2

1         a

2         b

but

test2

F1        F2     test     test2

1         a        2         3

2         b        4         6

3         a        6         9

4         b        8         12

test4

E1      E2

1         a

2         b

with duplicate test2 table in the table view.

Any explanation?

thank you for your time and consideration

[Miguel_Angel_Baeyens]

Hi,

After rewriting the script and before doing the second partial reload, did you fully reload the document to lose previous partial reloads? Or using noconcatenate in the second partial reload before "testtmp". It's concatenating the first LOAD instead of replacing it.

Hope that helps.

Miguel

Elaborating: the NOCONCATENATE keyword before the load of table "test2" makes the table "test2" survive the replace, since it's not concatenated, and therefore a new table "test2-1" is created, which in turn causes a unwanted synthetic table that shows both original "test2" values and new "test2-1". Removing that NOCONCATENATE the result is, as far as I can see, as expected. You don't need to force no concatenation in table "test2" because the name and number of fields is different from those in table "testtmp".

[lematiethibault]

Thank you for your answer, I really appreciate it, you are right the problem was linked with the "noconcatenate".

Even if, for me, that does not explain why qlikview make the table survive the replace.

Qlikview wasn't supposed to delete the replace table before to reload it? Or is that a bugg?

In my project, we use to write, as a rule, the keyword "NoConcatenate" before all the table we want to keep.

I'm kind of scared that removing it everywhere could cause major damage to the final result.

In add, another surprising thing, if after the first reload, I add the replace keyword on the table test3 but not on the test4 as following :

test3:

Replace

LOAD * INLINE [

    E1, E2

    3, a

    4, b

];

NoConcatenate

test4:

Load * Resident test3;

drop table test3;

after the partial reload I get the good result for test4:

test4

E1     E2

3       a

4       b

Any explaination?

[Miguel_Angel_Baeyens]

Hi,

I'd say that NOCONCATENATE and REPLACE, although allowed, are opposed: NOCONCATENATE is intended to force a table to keep in memory even if there is a table that has the same number and name of fields, whilst REPLACE is intended to drop a table from memory, then put into memory the new records.

In my opinion, this combination should not be allowed and the script should end in error, and I don't see any scenario where both functions should work together.

As a general rule, and from my experience, avoid as much as possible the partial reloads, and use switches instead (IF conditions based on the result of a variable for example).

Hope that makes sense.

Miguel

[lematiethibault]

I thought about using several if with conditions based on variable but I'm not sure that would be relevant in my case. The problem with the if option is that I will lose all the other table in memory.

In my previous example, let's say that I want to reload only test2 and keep the result of test4. Except creating 2 different applications, how would you do that?

For me, with the if option, I can't have both in memory.

Thanks again for your time and consideration Miguel.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

As a general rule, leverage on QVD files for all those middle steps, meaning save all tables in QVD files, then depending on the variables, load some of them or all of them (or none of them).

Think about this scenario: you extract Invoices on a monthly basis, the day 30 of each month. I store then each month in a different QVD file: Invoices201208.qvd for August, Invoices201209.qvd for September, etc.

Then I calculate the dates corresponding to the last 12 months and load the correspoding QVD files because of their name (in a loop, using wildcards...), but I keep the old files in the folder just in case. This allows me to easily load instead the last 24 months or only the last month, because there have been some modification. Yes, this requires to do several loads and concatenates, but loading from QVD files is so fast that it's still faster and easier than doing partial reloads.

What if I have to add data to the current month? I load the QVD with the original data, retrieve from database the new data and concatenate to the table from the QVD, then store the QVD witht the same name. Next time you will only have to load one file. However, it takes two steps, but performance is quite good.

Is this the only way, or the best way to load historic/new data? No, it's not the only way. And I don't know if this is the best way, but it works fine for me.

Hope some of the above makes at least little sense.

Miguel