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: 
Not applicable

for loop load issue

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

IDdcode
h1d1
h1d2
h1d3
h2d7
h2

d2

where as the qvd sample_agg has the following set

IDdcode
h1d1
h2d2
h2d3

Tbl.main_data

16 Replies
Not applicable
Author

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
Partner - Creator II
Partner - Creator II

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.

Not applicable
Author

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!

Not applicable
Author

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

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

Not applicable
Author

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