Updating a created table

Thu, 20 Feb 2014 16:59:48 GMT

Hi:

I would like to create a table that stores the DailyCustomerCount, which is derived from the sum of ActualCustCount. With each passing day, I'd like this created table to unsert a new row, with the previous day's DailyCustomerCount. Along with each row, I'd like to add the column that points to the day this Count corresponds to. Example:

(table view on 2.15.2014):

Date DailyCustomerCount

02.14.2014 45

(table view on 2.16.2014):

Date DailyCustomerCount

02.14.2014 45

02.15.2014 37

Any suggestions would be appreciated!

Thanks!

Re: Updating a created table

maxgro — Thu, 20 Feb 2014 20:48:33 GMT

I tried with test data, generated in qlikview script editor

look at the bold script

the logic should be the same with your data

// test data

Characters:

Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;

ASCII:

Load

if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,

Chr(RecNo()) as AsciiAlpha,

RecNo() as AsciiNum

autogenerate 255

Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;

Transactions:

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round( 10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 1000

While Rand()<=0.5 or IterNo()=1;

// adapt to your requirement: change field name, file name, table

DIRECTORY;

if FileSize('Tmp.qvd') > 0 then

trace ***** FILE EXISTS;

Tmp: load count(Dim3) as A, today() as B Resident Transactions;

concatenate (Tmp) LOAD * From Tmp.qvd (qvd) Where not Exists(B);

ELSE

trace ***** FILE NEW;

Tmp: load count(Dim3) as A, today() as B Resident Transactions;

ENDIF;

STORE Tmp into Tmp.qvd (qvd);

topic Updating a created table in QlikView

Updating a created table

Re: Updating a created table