Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sivarajs
Specialist II
Specialist II

store into qvd

Hi,

I am using for loop in my script and am storing into qvd based on iterations

But qvd have only one value because of overwriting

Is there any way to avoid overwriting

My script is looks like

for i=1 to 5

let var1=fieldvalue('custID',$(i));

Tmp1:

load category,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by category;

tmp2:

first 5 load caseid_count,category

resident Tmp1;

drop table Tmp1;

tmp3:

load stdev(caseid_count) as cat_std

resident tmp2;

drop table tmp2;

reg1:

load region,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by region;

reg2:

first 5 load caseid_count,region

resident reg1;

drop table reg1;

reg3:

load stdev(caseid_count) as cat_std

resident reg2;

drop table reg2;

cons:// trying to store red highlighted table into qvd

load * ,'' as i

resident tmp3;

drop table tmp3;

drop field i;

join

load * ,'' as i

resident reg3;

drop table reg3;

drop field i;

store cons into cons.qvd(qvd);

next

Sivaraj S

1 Solution

Accepted Solutions
vincent_ardiet
Specialist
Specialist

Hi,

First, it seems that you introduce the new field "i" in "cons" in order to avoid the automatic concatenation. In this case you can use the NoConcatenate key word before your load instruction instead, you have not do drop the fake field then.

But, for your problem, I think that you better had to store "cons" after the loop and to concatenate into "cons" in the loop.

You can avoid using tmp1 and tmp2 by using preceding load like :

Tmp1:

first 5 load caseid_count,category ;

load category,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by category;

Then, I don't understand the end of your script, for my point of view tmp3 and reg3 have the same structure so QV should concatenate reg3 into tmp3.

And, why are you joining (it means outer join in this case) the 2 tables in cons.

It's a bit confusing.

For me, you have to test the value of i (the loop index), if it's the first row use NoConcatenate before the Load instruction for "cons".

Then for the next iteration, use Concatenate(cons).

Something like that (but for me tmp3 and reg3 and the join are not clear, but it's the idea) :

for i=1 to 5

let var1=fieldvalue('custID',$(i));

Tmp1:

first 5 load caseid_count,category ;

load category,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by category;

tmp3:

noconcatenate load stdev(caseid_count) as cat_std

resident Tmp1;

drop table Tmp1;

reg1:

first 5 load caseid_count,region ;

load region,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by region;

reg3:

noconcatenate load stdev(caseid_count) as cat_std

resident reg2;

drop table reg2;

if i = 1 then

  cons:// trying to store red highlighted table into qvd

  noconcatenate load *

  resident tmp3;

else

  concatenate (cons) load *

  resident tmp3 ;

end if ;

drop table tmp3;

join (cons)

load *

resident reg3;

drop table reg3;

next

store cons into cons.qvd(qvd);

Regards,

Vincent

View solution in original post

7 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Check by changing this part

load * ,'' as i

resident tmp3;

drop table tmp3;

drop field i;

join

load * ,'' as i

resident reg3;

drop table reg3;

drop field i;

store cons into cons.qvd(qvd);

to

load * ,'' as i

resident tmp3;

join

load * ,'' as i

resident reg3;

drop field tmp3;

drop table reg3;

drop field i;

store cons into cons.qvd(qvd);

yvesqlik
Partner - Contributor III
Partner - Contributor III

- first step, create a inline tabelle for the iteration

cons:

LOAD * INLINE [

    cat_std

   

];

- second step,Bevor you load data in cons, you have to create

cons_temp:

load * ,'' as i

resident tmp3;

drop table tmp3;

drop field i;

join

load * ,'' as i

resident reg3;

drop table reg3;

drop field i;

-third step

concatenate(cons)

load * resident cons_temp;

drop table cons_temp;

next

store cons into cons.qvd(qvd);

hope my solution can help u

sivarajs
Specialist II
Specialist II
Author

@chelambu still it overwriting not storing as different rows in qvd

Anonymous
Not applicable

Before you store into QVD, can't you load the prior QVD and concatenate it to your final table before storing it again as a QVD?  This is the approach I usually use.

vincent_ardiet
Specialist
Specialist

Hi,

First, it seems that you introduce the new field "i" in "cons" in order to avoid the automatic concatenation. In this case you can use the NoConcatenate key word before your load instruction instead, you have not do drop the fake field then.

But, for your problem, I think that you better had to store "cons" after the loop and to concatenate into "cons" in the loop.

You can avoid using tmp1 and tmp2 by using preceding load like :

Tmp1:

first 5 load caseid_count,category ;

load category,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by category;

Then, I don't understand the end of your script, for my point of view tmp3 and reg3 have the same structure so QV should concatenate reg3 into tmp3.

And, why are you joining (it means outer join in this case) the 2 tables in cons.

It's a bit confusing.

For me, you have to test the value of i (the loop index), if it's the first row use NoConcatenate before the Load instruction for "cons".

Then for the next iteration, use Concatenate(cons).

Something like that (but for me tmp3 and reg3 and the join are not clear, but it's the idea) :

for i=1 to 5

let var1=fieldvalue('custID',$(i));

Tmp1:

first 5 load caseid_count,category ;

load category,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by category;

tmp3:

noconcatenate load stdev(caseid_count) as cat_std

resident Tmp1;

drop table Tmp1;

reg1:

first 5 load caseid_count,region ;

load region,count(caseid) as caseid_count

from hpd.qvd where custID='$(var1)' group by region;

reg3:

noconcatenate load stdev(caseid_count) as cat_std

resident reg2;

drop table reg2;

if i = 1 then

  cons:// trying to store red highlighted table into qvd

  noconcatenate load *

  resident tmp3;

else

  concatenate (cons) load *

  resident tmp3 ;

end if ;

drop table tmp3;

join (cons)

load *

resident reg3;

drop table reg3;

next

store cons into cons.qvd(qvd);

Regards,

Vincent

Not applicable

Hi Sivaraj Seeman,

You can handle this problem in 3 ways.

  • 1 way will be to while storing your qvds add a loop counter in qvd name so for example if you are loading when

for i = 1 to 5

store cons into cons_$(i).qvd(qvd);

       This way you will get 5 qvds and after the loop, by using

 Cons_final:       
Load * from Cons_*.qvd(qvd);
store cons_final into cons_final.qvd(qvd);

      You can concatenate all of them into a 1 final qvd.

  • 2nd way is more elegant and also easy to implement. see this sample script to see how it can be implemented for your scenario.    

set check = 1;

for i = 1 to 5

t1:

LOAD

1 as A AutoGenerate 1;

if $(check) <> 1 then

     concatenate (t1)    

     Load

               *

     from t1.qvd(qvd);

     store t1 into t1.qvd(qvd);  

  else

     store t1 into t1.qvd(qvd); 

  endif;

  DROP Table t1;

  set check = 0;

NEXT

      So, what this script does is that it will check if there is already a qvd generated by for loop in current script execution, if so then it will concatenate the QVD earlier generated to just genrated table and then store. Else if for loop is executing for 1st time then it will overwrite the QVD.

I believe this should work for your purpose.

  • 3rd way is a question from me that why don't you just save the QVD after the for loop?

       That way you will store QVD only once and it will contain data from all the instances of for loop because of        automatic concatenation of the tables generated.

Anyways, I believe these inputs should get you go.

Let me know, if any issue.

Thanks

Ashutosh

sivarajs
Specialist II
Specialist II
Author

@vincent n Ashutosh

Its working fine

Thanks!!!!!

Sivaraj S