for loop load issue

[#1]

I have the following code where I am trying to fetch id & dcode from a list and for each value run a load statement to a qvd. My problem is that my data has multiple dcodes for each id and even thought hlist contains these combinations, my qvd shows only 1 dcocde corrosponding to each ID. Can some one please explain why?

[hlist]:
LOAD
     ID, dcode
where CNT > 11;
//Identify all id, dcode combinations where count of records is > 11
SQL
    SELECT ID,
    dcode,
    CNT,
    RANK
FROM Tbl.`d_RANK`;


LET vhpCount = NoOfRows('hlist');
// Loop the dimensional information for each of the above hospital- DRG combination 
// where the number of cases is more than 10
For j = 1 to vhpCount 
     LET vid= FieldValue('ID',j);
     LET vd_code = FieldValue('dcode',j);

    [d_code_Agg]:
    LOAD  ID,
          dcode , 
          zip,
          APPLYMAP('v_SEX',SEX,'NA') AS SEX,
          APPLYMAP('v_RACE',RACE,'NA') AS RACE,
          APPLYMAP('v_DISP',DISP,'NA') AS DISP,
          APPLYMAP('v_FC',FC,'NA') AS FC,
          APPLYMAP('v_ETHNIC',ETHNIC,'NA') AS ETHNIC,
          num_cases;
  // WHERE num_cases > 5;
    SQL
    select  ID, 
            DRG , 
            zip,
            SEX, 
            RACE,
            DISP,
            FC,
            ETHNIC,
  count(*) as num_cases
  from Tbl.main_data
  where ID    = '$(vid)'
    and dcode = '$(vd_code)'
  group by ID,dcode, zip,SEX, RACE,DISP,FC, ETHNIC;


    STORE d_code_Agg INTO 'lib://tname/sample_agg.qvd' ; 


NEXT j;

This is what the tables look like

hlist

ID	dcode
h1	d1
h1	d2
h1	d3
h2	d7
h2	d2

where as the qvd sample_agg has the following set

ID	dcode
h1	d1
h2	d2
h2	d3

Tbl.main_data

[#2]

I checked for selections, I tried loading into a new file as well.

If name.qvd is already present at the path then is the data appended to it or overwritten?

[marcus_sommer]

A qvd will be always overwritten but even if your store is within the loop (better would be outside) you should get all records because of the autoconcatenation from loadings which have the same data-structure. But maybe it's useful to force these concatenation with a statement like:

....

LET vd_code = FieldValue('dcode',j); 

let vConcat = if(noofrows('d_code_Agg') = 0, '[d_code_Agg]:', 'concatenate([d_code_Agg])');

//[d_code_Agg]: 

$(vConcat)

LOAD  ID, 

....

- Marcus

[shubham_singh]

Placing store statement outside loop will not give you last value, tables of same structure gets auto concatenated to existing table so you will have all data in single table after loop end and you can store that.

However I am guessing you don't need loop to perform your desired task.

Let me know your desired output and some sample data.

[#5]

I have figured, what is happening. The first file 'hlist' stores hid and dcode as list of unique values. So when I use the loop, the 10th value for dcode is not the dcode corresponding to 10th hid but the 10th unique value, which is driving me crazy!

[#6]

shubham.singh‌ I want to load the data in Tbl.main_data for each value obtained in hlist. So for id =h1 and dcode = d1 I have to fetch the records, then for h1 and d2  combination and so on.

[marcus_sommer · Accepted solution]

You need to loop through the hlist-table and not through the fieldvalues which are always a distinctlist of fieldvalues. This could be done per Peek() or Previous() ? for in your case for example:

....

LET vhpCount = NoOfRows('hlist'); 

For j = 1 to vhpCount  

     LET vid= peek('ID', j, 'hlist'); 

     LET vd_code = peek('dcode', j, 'hlist'); 

....

- Marcus

[#8]

That indeed was my problem. Thank you for your time  marcus_sommershubham.singh