Sales Transactions ETL - promote across line items

[#1]

I have a data set that looks like the following:

Check No	sequence	Type	Ext_Price
216863	1	Sale	1.99
216863	2	Void	-2.99
216863	3	Sale	3.99
216863	4	Tender	5.98
216864	1	Sale	1.5
216865	2	Sale	3.5
216866	3	Tender	5

What I want is to add a column on load for the sum of ext_price by type (so a column for TRX_SALE and TRX_VOID) - to look as followed:

Check No	sequence	Type	Ext_Price	TRX_SALE	TRX_VOID
216863	1	Sale	1.99	5.98	-2.99
216863	2	Void	-2.99	5.98	-2.99
216863	3	Sale	3.99	5.98	-2.99
216863	4	Tender	5.98	5.98	-2.99
216864	1	Sale	1.5	5	0
216865	2	Sale	3.5	5	0
216866	3	Tender	5	5	0

I know how to do an if statement for the load script to put the Ext_Price in the correct column, but only for that line (sequent) only.  I want to put the total sum for the check#, promoted accross all sequence lines.

Is this possible in Qlikview's ETL?  Could someone either help me with an example of the code to do this, or point me towards a reference for this?

thanks

[markmccoid · Accepted solution]

I would do something like this:

Table_work:

LOAD

     CheckNo,

     sequence,

     Type,

     Ext_Price

FROM YourTable;

TRX_SALE_Table:

LOAD

     CheckNo,

     Sum(Ext_Price) as TRX_SALE

Resident Table_work

Group by CheckNo

Where Type = 'Sale';

TRX_VOID_Table:

LOAD

     CheckNo,

     Sum(Ext_Price) as TRX_VOID

Resident Table_work

Group by CheckNo

Where Type = 'Void';

FinalTable:

LOAD *

Resident Table_work;

JOIN

LOAD *

Resident TRX_SALE_Table;

JOIN

LOAD *

Resident TRX_VOID_Table;

drop table Table_work, TRX_SALE_Table, TRX_VOID_Table;

Usually when I do this kind of stuff, there is a little bit of experimenting to get it right, but hopefully this will get you on your way.

[#3]

Thanks - huge help.

Although I'm having trouble joining - it keeps saying it can't find trx_sale_table for the join or the drop clause.  In the debug window, it lists trx_void_table twice.  Any idea what I'm doing wrong?

thanks

[#4]

there was supposed to be a screenshot in there.  oops.

trx_void_table:

LOAD

    [Unique Check],

     sum(  [Extended Price]) as TRX_SALE

     Resident TLDE

     Where trx_type = 'Item Void'

     Group by [Unique Check];

trx_sale_table:

LOAD

    [Unique Check],

     sum(  [Extended Price]) as TRX_SALE

     Resident TLDE

     Where trx_type = 'Regular Sale' or 'Item Discount'

     Group by [Unique Check];

finaltable:

LOAD *

Resident TLDE;

JOIN

Load *

Resident trx_sale_table;

Load *

Resident trx_void_table;

drop table trx_void_table, trx_sale_table, TLSE;

[markmccoid]

You have named your sum(  [Extended Price]) as TRX_SALE for both the sale and the void table.  You probably want to start by renaming the void SUM to TRX_VOID.

You can attach the whole QVW so we could see the whole script if there are still issues.

[#6]

Thanks - the naming at least made the load work properly.  I still can't get the files to join - right now I'm using them as seperate tables linked by [Unique Check].  Good enough for now, since I'm still in the proof of concept stage - I'll have to come back to cleaning up the data model later.

Thanks