Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inter Records Creation

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

2 Replies
Not applicable
Author


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;





Not applicable
Author

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