topic Re: Add calulcated fields to a table after its loaded in QlikView

Add calulcated fields to a table after its loaded

Wed, 03 Oct 2012 19:03:12 GMT

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;

Re: Add calulcated fields to a table after its loaded

Gysbert_Wassenaar — Wed, 03 Oct 2012 19:53:00 GMT

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;

Re: Add calulcated fields to a table after its loaded

Thu, 04 Oct 2012 14:17:09 GMT

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

Re: Add calulcated fields to a table after its loaded

Gysbert_Wassenaar — Thu, 04 Oct 2012 14:27:44 GMT

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

Re: Add calulcated fields to a table after its loaded

Thu, 11 Oct 2012 15:19:47 GMT

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.

Re: Add calulcated fields to a table after its loaded

johnw — Thu, 11 Oct 2012 19:50:15 GMT

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,