large set of data and autonumber() function

[#1]

Hi everybody

I'm having troubles whit large set of data and the autonumber() function.

I have 3 transactions tables linked by 4 key fields: Z, POS,DOCTYPE,DOCNRO

The first table contains the DATE and TOTAL AMOUNT (among other data) (3.800.000 rows )

The second table contains de PRODUCT, QUANTITY and PRICE (don't have de DATE field) (35.000.000 rows)

The third table has data from the payment (5.000.000 rows)

I've tried reading them from qvd files and linking them by the key field:

autonumber(Z&' '&POS&' '& DOCTYPE&' '& DOCNRO) but the reload takes 10 times more that if I let qlikview to create a synthetic Key with the 4 fields. (I cant'n work with autonumber(Z&POS&DOCTYPE&DOCNRO) because of the format of the Z and DOCTYPE data)

The fact is that I want to create 2 qvd files

- one with all the data from the second table + DATE

- and the other with all the data from the third table + DATE

I've tried with "mapping load" and with "left or right join" before de store function, but take to long.

Does anybody know a better way to force a synthetic key that with autonumber()?

[johnw]

So you only want two resulting tables, not three, and you want to store them as QVDs. And you're dropping the TOTAL AMOUNT field completely? Sounds like you want to do this:

[Two]:
LOAD Z, POS, DOCTYPE, DOCNRO, other fields from the second table
FROM source two
;
LEFT JOIN ([Two])
LOAD Z, POS, DOCTYPE, DOCNRO, DATE
FROM source one
;
STORE [Two] INTO Two.qvd (QVD);
DROP TABLE [Two];

[Three]:
LOAD Z, POS, DOCTYPE, DOCNRO, other fields from the third table
FROM source three
;
LEFT JOIN ([Three])
LOAD Z, POS, DOCTYPE, DOCNRO, DATE
FROM source one
;
STORE [Three] INTO Three.qvd (QVD);
DROP TABLE [Three];

If that's what you're saying is taking too long, I'm not sure why that would be.

If you want to see the results, then don't drop the tables, and just let QlikView build the synthetic key. No reason to spend ten times as long doing something manually that QlikView does for you automatically.

[#3]

Thank you Jhon, I'm trying to save time in the reload process because one of the tables has 27 millions rows only for 2 years of data and I only has 1 hour for the use of the server. I've created a view in sql and take a little less.