7 Replies Latest reply: May 31, 2012 1:14 AM by Sivaraj Seeman RSS

store into qvd

Sivaraj Seeman

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

  • store into qvd
    Celambarasan Adhimulam

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

  • store into qvd
    yvesqlik

    - 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

  • Re: store into qvd
    Vincent Ardiet

    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

  • store into qvd
    Ashutosh Paliwal

    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