topic store into qvd in QlikView

store into qvd

sivarajs — Wed, 30 May 2012 13:49:06 GMT

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);

Sivaraj S

store into qvd

CELAMBARASAN — Wed, 30 May 2012 14:17:00 GMT

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);

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);

store into qvd

yvesqlik — Wed, 30 May 2012 14:17:07 GMT

- 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;

store cons into cons.qvd(qvd);

hope my solution can help u

store into qvd

sivarajs — Wed, 30 May 2012 14:40:05 GMT

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

store into qvd

Anonymous — Wed, 30 May 2012 14:46:13 GMT

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.

Re: store into qvd

vincent_ardiet — Wed, 30 May 2012 15:33:30 GMT

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;

store cons into cons.qvd(qvd);

Regards,

Vincent

store into qvd

Wed, 30 May 2012 19:17:49 GMT

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

store into qvd

sivarajs — Thu, 31 May 2012 05:14:28 GMT

@vincent n Ashutosh

Its working fine

Thanks!!!!!

Sivaraj S