Add calulcated fields to a table after its loaded

[#1]

I have a table that I am loading and I want to add some calculated fields to the table during the load script.

My table has unique records based on Customer,Item,Year, Month which are YTD based numbers.

So after I do the initial load of the fields I want to add fields to subtract the prior months value from the current months value to get a differance.

CUSTOMER,ITEM,YEAR,MONTH,Gross Sales

A       ,001 ,2012,01   ,100
A       ,001 ,2012,02   ,175
A       ,001 ,2012,03   ,275
ETC....

What I am looking to do

CUSTOMER,ITEM,YEAR,MONTH,Gross Sales,Gross Sales Monthly Differance
A       ,001 ,2012,01   ,100       ,0
A       ,001 ,2012,02   ,175       ,75
A       ,001 ,2012,03   ,275       ,100

Since the table is YTD january has no value

Februrary value is 75 since 175-100 = 75

March value is 100 since 275-175 = 100

etc

My Load Script for the Table.

LOAD    testCustomerNum,

testItem,

testGrossSales,

testDeferedRevenueAdjustment,

testGrossSalesAdj,

testTotalGrossSales,

testYear,

testMonth;

SQL SELECT testCustomerNum,

testItem,

testGrossSales,

testDeferedRevenueAdjustment,

testGrossSalesAdj,

testTotalGrossSales,

testYear,

testMonth

FROM DataStorage.dbo.testtable;

[Gysbert_Wassenaar]

Try this:

TableTemp:

LOAD    testCustomerNum,

testItem,

testGrossSales,

testDeferedRevenueAdjustment,

testGrossSalesAdj,

testTotalGrossSales,

testYear,

testMonth;

SQL SELECT testCustomerNum,

testItem,

testGrossSales,

testDeferedRevenueAdjustment,

testGrossSalesAdj,

testTotalGrossSales,

testYear,

testMonth

FROM DataStorage.dbo.testtable;

TableResult:

load

  testCustomerNum,

testItem,

testGrossSales,

testDeferedRevenueAdjustment,

testGrossSalesAdj,

testTotalGrossSales,

testYear,

testMonth,

testTotalGrossSales - peek(testTotalGrossSales) as GrossMontlyDifference

resident TableTemp;

drop table TableTemp;

[#3]

The problem with peek is that it does not take into account that the records before may not be for the right customer, item, year, month sequence.

so i might have

A     1     2012     1     200

A     1     2012     2     220

B     1     2012     1     25

B     1     2012     2     75

so for customer B the first record would be wrong as it would pick up the 220 from the prior record and I would end up with this

A     1     2012     1     200     0

A     1     2012     2     220    20

B     1     2012     1     25     -195

B     1     2012     2     75     50

instead of

A     1     2012     1     200     200  (you get 200 - 0 since there is no record combo before)

A     1     2012     2     220    20

B     1     2012     1     25     25  (you get 25 - 0 since there is no record combo before)

B     1     2012     2     75     50

[Gysbert_Wassenaar]

In that case it's probably better solved with expressions in the charts and not in the load script.

[#5]

I think that I am going to end up actualy doing a SQL subquery on the load and have SQL Server do the work instead of Qlikview.

Thanks for the info.

[johnw]

Doing it in SQL is fine, but you CAN do it in QlikView.  Just like you can peek() at the previous row to get the sales, you can peek() at the previous row to see if the previous row has matching values for customer, item, etc.  Probably something like this:

testTotalGrossSales - if(peek(testCustomerNum)=testCustomerNum and ..., peek(testTotalGrossSales), 0) as GrossMonthlyDifference,