Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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