For each row in a loaded table, make some concatenations

[#1]

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

....

[pover · Accepted solution]

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

[#3]

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