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

How to store all For loop data in single QVD

gireesh1216 — Wed, 25 Nov 2020 16:16:04 GMT

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;

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

lironbaram — Wed, 14 Mar 2018 06:26:31 GMT

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;

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

gireesh1216 — Wed, 14 Mar 2018 06:39:43 GMT

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;

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

lironbaram — Wed, 14 Mar 2018 07:49:13 GMT

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?

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

gireesh1216 — Wed, 14 Mar 2018 08:04:25 GMT

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.

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

lironbaram — Wed, 14 Mar 2018 08:07:41 GMT

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

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

jyothish8807 — Wed, 14 Mar 2018 08:25:41 GMT

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,