Incremental Load of FlatTable

Mon, 21 Sep 2009 19:12:38 GMT

I have a table which has the following structure:

ProdID, CustomerID, Year, Amount1, Marge1, Cost1, Amount2, Marge2, Cost2, Amount3, Marge3, Cost3,...,...,...,...

Where 1,2,3,.. are the months. So the table is in an odd structure.

What would be the best way to incremental Load that table.

My approach:


table1:
LOAD
ProdID,
CustomerID,
Year,
Amount1,
Marge1,
 Cost1
FROM source;

CONCATANATE(tabe1)
LOAD
ProdID,
CustomerID,
Year,
Amount1,
Marge1,
 Cost1
FROM source;

But this gives me too many ProdID's and CustomerID's. Is that a problem?
Any help is appreciated!

Incremental Load of FlatTable

johnw — Tue, 22 Sep 2009 02:12:10 GMT

What do you want the final result to be? Something like this?

ProdID, CustomerID, Year, Month, Amount, Marge, Cost

If so, look into doing a CROSSTABLE load. There might be an easier way, and I probably have bugs, but perhaps something like this:

Raw:
LOAD
recno() as Recno
,ProdID
,CustomerID
,Year
,Amount1
,Amount2
...
,Amount12
,Marge1
,Marge2
...
,Marge12
,Cost1
,Cost2
...
,Cost12
FROM Source
;
New:
CROSSTABLE (Month,Amount,4)
LOAD
Recno
,ProdID
,CustomerID
,Year
,Amount1 as 1
,Amount2 as 2
...
,Amount12 as 12
RESIDENT Raw
;
LEFT JOIN (New)
CROSSTABLE (Month,Marge)
LOAD
Recno
,Marge1 as 1
,Marge2 as 2
...
,Marge12 as 12
RESIDENT Raw
;
LEFT JOIN (New)
CROSSTABLE (Month,Cost)
LOAD
Recno
,Cost1 as 1
,Cost2 as 2
...
,Cost12 as 12
RESIDENT Raw
;

As for turning that into an incremental load, I'd need more information. Is "source" a file? A database? A QVD? How is data added to "source"? On what schedule? Can records be deleted from "source"? Can they be changed? Or can they only be added? What advantage are you hoping to gain by going to an incremental reload?

topic Incremental Load of FlatTable in QlikView

Incremental Load of FlatTable

Incremental Load of FlatTable