Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

gireesh1216
Contributor II

How to store all For loop data in single QVD

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;

6 Replies
Partner
Partner

Re: How to store all For loop data in single QVD

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
Contributor II

Re: How to store all For loop data in single QVD

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;

Partner
Partner

Re: How to store all For loop data in single QVD

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
Contributor II

Re: How to store all For loop data in single QVD

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.

Partner
Partner

Re: How to store all For loop data in single QVD

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
Honored Contributor II

Re: How to store all For loop data in single QVD

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

Best Regards,
KC