Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a table with the values
TableB:
num quantity Frecuency nextdate(DD/MM/YYYY) Value
1 3 monthly 01/01/2011 100
2 4 weeklt 01/01/2011 50
.....
I want to generate a table C with this result
num nextdate value
1 01/01/2010 100
1 01/02/2010 100
1 01/03/2010 100
2 01/01/2010 50
2 08/01/2010 50
2 15/01/2010 50
2 22/01/2010 50
I have a previus table named A and i want to concatenate the table C reading the table B, how do i this?
how can i do this in qlikview?
for each row in tableB do
for i = 1 to quantity do
if frecuency = monthly then
concatenate tableA load num, addmonths(nextdate,frecuency), value from tableB
....
Here's the script you would need. Regards.
Original_Table:
LOAD * INLINE [
num,quantity,frecuency,nextdate,Value
1,3,monthly,01/01/2011,100
2,4,weekly,01/01/2011,50
];
let vRowCount = NoOfRows('Original_Table');
for i=0 to $(vRowCount)-1
let vQuantity = peek('quantity',$(i),'Original_Table');
let vNum = peek('num',$(i),'Original_Table');
for j=0 to $(vQuantity)-1
New_Table:
LOAD num,
date(if(frecuency='monthly', addmonths(date(date#(nextdate)),$(j)),
if(frecuency='weekly', date(date#(nextdate))+(7*$(j))))) as date,
Value as newValue
Resident Original_Table
Where num = $(vNum);
next
next
Here's the script you would need. Regards.
Original_Table:
LOAD * INLINE [
num,quantity,frecuency,nextdate,Value
1,3,monthly,01/01/2011,100
2,4,weekly,01/01/2011,50
];
let vRowCount = NoOfRows('Original_Table');
for i=0 to $(vRowCount)-1
let vQuantity = peek('quantity',$(i),'Original_Table');
let vNum = peek('num',$(i),'Original_Table');
for j=0 to $(vQuantity)-1
New_Table:
LOAD num,
date(if(frecuency='monthly', addmonths(date(date#(nextdate)),$(j)),
if(frecuency='weekly', date(date#(nextdate))+(7*$(j))))) as date,
Value as newValue
Resident Original_Table
Where num = $(vNum);
next
next
Hi there,
You can use the following code also:
Original_Table:
LOAD * INLINE [
num,quantity,frecuency,nextdate,Value
1,3,monthly,01/01/2011,100
2,4,weekly,01/01/2011,50
];
Table:
Load num, if(frecuency = 'monthly',
addmonths(date#(nextdate,'DD/MM/YYYY'), iterno() - 1),
date(num(date#(nextdate,'DD/MM/YYYY')) + (iterno()-1) * 7) ) as date,
Value as newvalue
resident Original_Table
while iterno() <= quantity;
Regards