topic Inter Records Creation in QlikView

Inter Records Creation

Thu, 17 Feb 2011 13:04:45 GMT

Hi Qlik Community,

I have a bit of an issue that I am trying to solve and hope that you guys might be able to assist me.

Basically I have a simple data file with contract, period start, period end, quantity. Basically for each contract I want to create a single line for each date between the period. E.g. if my period start is 01/04/2010 and my period end is 04/04/2011 then I want to create 4 records and then divide the quantity by 4 to give an equal weighting to each date within the period.

I have tried to get this working by using For-Next loops and variables (using the Peek function) but I can't quite get it working correctly. Here is the source code:



Contracts_Temp:
LOAD



rowno

 () AS [Row Number]
,













 





 


 

















 





 








Contract


,
[Period Start]


,
[Period End]


,
NUM

 ([Period End]) - NUM([Period Start]) + 1 AS [Period Length]
,
Value
FROM

(
 ooxml, embedded labels, table is
 Sheet1);
LET



 vContractIterationNumber
 = 1;
FOR



 x = 1 TO NoOfRows
('Contracts_Temp')
LET



 vPeriodLength = Peek('Period Length',$(vContractIterationNumber)-1,Contracts_Temp
);
LET



 vRecordIterationNumber
 = 1;
FOR



 y = 1 TO
$(vPeriodLength)
LOAD
Contract

 AS [Contract Header]
,
Contract

 & $(vRecordIterationNumber) AS Contract
,
DATE

 (NUM([Period Start]) + $(vRecordIterationNumber) - 1,'DD/MM/YYYY') AS [Period Actual]
,
MONTH

 (DATE(NUM([Period Start]) + $(vRecordIterationNumber) - 1,'DD/MM/YYYY')) AS [Month Actual]
,
[Period Length]


,
Value

 / [Period Length] AS
Value
RESIDENT


 Contracts_Temp;
LET



 vRecordIterationNumber = $(vRecordIterationNumber)
 + 1;
NEXT



 y
;
LET



 vContractIterationNumber = $(vContractIterationNumber)
 + 1;
NEXT



 x
;
DROP



 TABLE
 Contracts_Temp;

It will happily run the script fine, but becuase of the loop and where it is in the code, it takes the last contract it loads to being the dominant one and loads records based on the period length from that record, as opposed to the period length of the reocrd it's loading.

Hope someone can assist.

Cheers,

Paul Dutton

Bellis-Jones Hill

Inter Records Creation

Thu, 17 Feb 2011 13:06:05 GMT


Contracts_Temp:
LOAD
 rowno() AS [Row Number],
 Contract,
 [Period Start],
 [Period End],
 NUM([Period End]) - NUM([Period Start]) + 1 AS [Period Length],
 Value
FROM

(ooxml, embedded labels, table is Sheet1);
LET vContractIterationNumber = 1;
FOR x = 1 TO NoOfRows('Contracts_Temp')
 LET vPeriodLength = Peek('Period Length',$(vContractIterationNumber)-1,Contracts_Temp);
 LET vRecordIterationNumber = 1;
 FOR y = 1 TO $(vPeriodLength)
 LOAD
 Contract AS [Contract Header],
 Contract & $(vRecordIterationNumber) AS Contract,
 DATE(NUM([Period Start]) + $(vRecordIterationNumber) - 1,'DD/MM/YYYY') AS [Period Actual],
 MONTH(DATE(NUM([Period Start]) + $(vRecordIterationNumber) - 1,'DD/MM/YYYY')) AS [Month Actual],
 [Period Length],
 Value / [Period Length] AS Value
 RESIDENT Contracts_Temp;
 LET vRecordIterationNumber = $(vRecordIterationNumber) + 1;
 NEXT y;
LET vContractIterationNumber = $(vContractIterationNumber) + 1;
NEXT x;

DROP TABLE Contracts_Temp;

Inter Records Creation

Fri, 18 Feb 2011 14:14:59 GMT

Hi Again,

I have solved this issue now, just some bad coding practices from myself I think. For anyone who has seen this post and is interested on how it worked out in the end, here is the code I used:


Contracts_Temp:
LOAD
 Contract,
 [Period Start],
 [Period End],
 NUM([Period End]) - NUM([Period Start]) + 1 AS [Period Length],
 Value
FROM

(ooxml, embedded labels, table is Sheet1);
LET vContractIterationNumber = 1;
FOR x = 1 TO NoOfRows('Contracts_Temp')
 LET vContract = Peek('Contract',$(vContractIterationNumber)-1,'Contracts_Temp');
 LET vPeriodLength = Peek('Period Length',$(vContractIterationNumber)-1,'Contracts_Temp');
 LET vPeriodStart = NUM(Peek('Period Start',$(vContractIterationNumber)-1,'Contracts_Temp'));
 LET vPeriodEnd = Peek('Period End',$(vContractIterationNumber)-1,'Contracts_Temp');
 LET vValue = Peek('Value',$(vContractIterationNumber)-1,'Contracts_Temp');
 LET vRecordIterationNumber = 1;
 FOR y = 1 TO $(vPeriodLength)
 Contracts:
 LOAD DISTINCT
 '$(vContract)' AS [Contract Header],
 '$(vContract)' & $(vRecordIterationNumber) AS Contract,
 DATE(NUM($(vPeriodStart) + $(vRecordIterationNumber) - 1),'DD/MM/YYYY') AS [Period Actual],
 MONTH(DATE(NUM($(vPeriodStart)) + $(vRecordIterationNumber) - 1,'DD/MM/YYYY')) AS [Month Actual],
 $(vPeriodLength) AS [Period Length],
 $(vValue) / $(vPeriodLength) AS Value
 RESIDENT Contracts_Temp;
 LET vRecordIterationNumber = $(vRecordIterationNumber) + 1;
 NEXT y;
 LET vContractIterationNumber = $(vContractIterationNumber) + 1;
NEXT x;

DROP TABLE Contracts_Temp;

Cheers,

Paul Dutton

Bellis-Jones Hill