Completely baffled by resident concatenation

[hobanwashburne]

I perform the same series of steps in two different places. It works the first time, but fails the second.

The first time I create a temporary table "Temp01" and concatenate to an existing table "FY2014" via

CompleteTableA:

Load

     <field1>,

     <field2>

From

[... location\FY2014.qvd]

(qvd);

Concatenate

Load *

Resident Temp01;

Drop Table Temp01;

The result is one table named CompleteTableA

The second time I perform the same steps

CompleteTableB:

Load

     <field1>,

     <field2>

From

[... location\ReceiptsFY2014.qvd]

(qvd);

Concatenate

Load *

Resident Temp02;

The resultant table is names Temp02 rather than CompleteTableB (???) although the data is correct.

Also if I change the last concatenate to

Load *,

'x' as Test

Resident Temp02;

The resultant table is CompleteTableB (with the additional field)

[vgutkovsky]

Scott, the reason that's happening is because of auto-concatenation. If you load a table with the exact same fields as a table that's already loaded, the second table will be automatically concatenated onto the first. So that means when you have a resulting table "Temp02" the data is not correct (check again). When you first load CompleteTableB, since it has the same fields as CompleteTableA, it is automatically added to CompleteTableA. When you perform the next step (concatenating from Temp02), since you didn't specify the table to concatenate onto, it just concatenated to the previous table in the data model that existed (whatever was the last table before CompleteTableB to be loaded). It's always a good idea to explicitly say which table you want to concatenate onto to prevent surprised like these. The syntax for that would be:

CONCATENATE (CompleteTableB) LOAD * RESIDENT Temp02;

By the way, to break away from the default auto-concatenation behavior, you can use the "NOCONCATENATE" prefix:

CompleteTableB:

NOCONCATENATE LOAD

     <field1>,

     <field2>

From

[... location\ReceiptsFY2014.qvd] (qvd);

Regards,

Vlad

[hobanwashburne]

Vlad,

I completely understand and agree with what you're saying, however my problem persists. I have since copied the second half of my script to a new file (there is no longer CompleteTableA in the code). If I explicitly define the concatenation:

Concatenate (CompleteTableB)

Load *

Resident Temp02;

I receive a "CompleteTableB not found" error

If I add a Noconcatenate clause prior to the load of CompleteTableB Qlikview runs to that point and becomes unresponsive.

[vgutkovsky]

There must be another table (other than CompleteTableA) that has the exact same fields as CompleteTableB in your new code. If you can't find it, attach the log file from your app here and I'll take a look.

Vlad

[JonnyPoole]

Can you post your new script ? 

[sundarakumar]

Hi,

I would like t know what all are the fields present in ReceiptsFY2014 and temp 2.

with ur statement "If I add a Noconcatenate clause prior to the load of CompleteTableB Qlikview runs to that point and becomes unresponsive." I guess there are lot of common fields between these two tables. since it is no concatenate it is trying to create a big synthetic key which is making qlikview to go unresponsive.

Try

Qualify *;

before the load of CompleteTableB in the second qlikview file and try to load temp 2 with no concatenate. Guess we will get an idea of what is wrong..

-Sundar

[hobanwashburne]

As much as I would like to post my script and ask for help I'm working on with live data and don't have time right now to strip everything out. Maybe I should explain exactly what it is I am trying to do (and have done successfully in the past which is why this is so frustrating!)

I first use PEEK to open up the RecieptsFY2014 and get the max date that it contains from a specific field. Then the table is dropped. <Result: 0 tables>

Next I load from the AS400 all of the new data between the max date read from the original file and yesterday and store that into Temp01. <Result: 1 Table>

Next I perform all of my Mapping and Joins to resident Temp01 creating Temp02. Then I drop Temp01. <Result: 1 Table>

Here's where the issue comes in.

Next I load the original ReceiptsFY2014 (again, but all fields this time) and try to concatenate Temp02 with the desired result of 1 table.

Sundar, I agree that the reason that QV becomes unresponsive is because it is trying to create a synthetic key for every field and if I try Qualify I get exactly what I would expect: 2 tables: Temp02 and ResultsFY2014.

[vgutkovsky]

Try dropping Temp02 at the end.

Vlad

[hobanwashburne · Accepted solution]

I copied my code into another (completely empty this time) file, and now it works as intended. I doubt that I will ever figure out what was going wrong with the original. Regardless, thank you everyone for the help.