Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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);
- 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
@chelambu still it overwriting not storing as different rows in qvd
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.
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
Hi Sivaraj Seeman,
You can handle this problem in 3 ways.
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.
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.
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
@vincent n Ashutosh
Its working fine
Thanks!!!!!
Sivaraj S