Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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?
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.
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
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