How to store all For loop data in single QVD

[gireesh1216]

Hello Friends,

In the below script I am using for loop.I want to store all data into single qvd.

Can u please help on that.

DATES:

LOAD * Inline [
Start_Date,End_Date
01-Aug-17,31-Jul-18
01-Dec-17,30-Nov-18
01-Jan-18,31-Dec-18

];

Let vInlinCount = NoOfRows('DATES:');

//OPEN FOR LOOP
For i = 0 To $(vInlinCount)-1

//GET THE DATE IN VARIABLES
LET vSP_START_DT = Peek('Start_Date', $(i), 'DATES:');
LET vSP_END_DT = Peek('End_Date', $(i), 'DATES:S');

DBDATA:
SELECT * FROM EMPLOYEE WHERE LOB='1216' AND NVL (CREATED_DT,INIT_DT) BETWEEN TO_DATE('$(vSP_START_DT)','DD-MON-YY') AND TO_DATE('$(vSP_END_DT)','DD-MON-YY');

if $(i)=0 then
STORE DBDATA into DATA_gireesh.qvd(qvd);
ELSE
STORE DBDATA into DATA_gireesh1.qvd(qvd);
ENDIF

//Storing all qvds in single qvd

if $(i)>0 then
NoConcatenate

TEMP:
LOAD *
FROM
DATA_gireesh.qvd]
(qvd);
Concatenate
LOAD *
FROM
DATA_gireesh1.qvd]
(qvd);

STORE TEMP into DATA_gireesh.qvd(qvd);

ENDIF

Next i
DROP Table DBDATA;

[lironbaram]

as it seems that you load the same table 3 times

you can just loop the  load phase and you'll get one table in the end

because Qlik will automatically concat the tables

so your script can look something like

DATES:

LOAD * Inline [
Start_Date,End_Date
01-Aug-17,31-Jul-18
01-Dec-17,30-Nov-18
01-Jan-18,31-Dec-18

];

Let vInlinCount = NoOfRows('DATES:');

//OPEN FOR LOOP
For i = 0 To $(vInlinCount)-1

//GET THE DATE IN VARIABLES
LET vSP_START_DT = Peek('Start_Date', $(i), 'DATES:');
LET vSP_END_DT = Peek('End_Date', $(i), 'DATES:S');

DBDATA:
SELECT * FROM EMPLOYEE WHERE LOB='1216' AND NVL (CREATED_DT,INIT_DT) BETWEEN TO_DATE('$(vSP_START_DT)','DD-MON-YY') AND TO_DATE('$(vSP_END_DT)','DD-MON-YY');

  next i

if $(i)=0 then
STORE DBDATA into DATA_gireesh.qvd(qvd);

DROP Table DBDATA;

[gireesh1216]

vInlinCount=3 Means For loop rotate 3 times. Each time we need to store some where in QVD.

In ur solution, Data stores in If I=0,Other wise NOO.

We need to store data in All conditions in single QVD.

if $(i)=0 then
STORE DBDATA into DATA_gireesh.qvd(qvd);

DROP Table DBDATA;

[lironbaram]

my mistake

remove that line

than it'll store the full table after the loop in one step

why do you want to store it 3 times?

[gireesh1216]

Thanks for ur reply

I am not storing 3times.For example

If I=0 getting 10 records

if I=1 getting 5 records

if I=2 getting 25 records

Here I want to store all data 10+5+25 into single qvd.

if u write single store statement we wil get last loop data(25 records).But we want to store all data.

[lironbaram]

no

it will store all the records look at the script i worte the loop is after the reload

the store only happens after the loop is completed

so it'll store all the data

[jyothish8807]

Try like this: Dont make it complicated

DATES:

LOAD * Inline [
Start_Date,End_Date
01-Aug-17,31-Jul-18
01-Dec-17,30-Nov-18
01-Jan-18,31-Dec-18

];

Let vInlinCount = NoOfRows('DATES:');

//OPEN FOR LOOP
For i = 0 To $(vInlinCount)-1

//GET THE DATE IN VARIABLES
LET vSP_START_DT = Peek('Start_Date', $(i), 'DATES:');
LET vSP_END_DT = Peek('End_Date', $(i), 'DATES:S');

DBDATA:

SELECT * FROM EMPLOYEE WHERE LOB='1216' AND NVL (CREATED_DT,INIT_DT) BETWEEN TO_DATE('$(vSP_START_DT)','DD-MON-YY') AND TO_DATE('$(vSP_END_DT)','DD-MON-YY');

Next i

Noconcatenate

New_DBDATA:

Load

*

resident DBDATA;

DROP Table DBDATA;

store New_DBDATA into DATA_gireesh.qvd(qvd);

Drop table New_DBDATA;

Br,

KC