Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
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

1 Solution

Accepted Solutions
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

View solution in original post

16 Replies
marcus_sommer

There are two things noticeable. The first is a DISTINCT which might needed to prevent multiple record per ID and dcode whereby it seems not necessary in your case:

[hlist]: 

LOAD DISTINCT

....

The other point is the place of your store-statement which stored the table by each iteration in the same file and this table increased by each iteration per auto-concatenation without dropping the previous loaded table. If you want a separate qvd for each iteration you should add your variables within the filename and then dropping the table or place the store-statement just after the loop.

But I'm not sure if both points touch your described abnormalities. For this I would run this within the debugger to see how many iterations happens and how your variables look like by them.

- Marcus

shubham_singh
Partner - Creator II
Partner - Creator II

1. Your code is incorrect, it should show an error while loading, there in no dcode in SQL Select Statement.

2. Write store statement outside of loop.

3. Looks like you are trying to calculate number of cases for every unique combination of ID,dcode,zip,SEX, RACE,DISP,FC, ETHNIC. You don't need a loop for that. Just use exists function to filter records if hlist contains less unique combinations of ID and dcode.

4. Fetching data from any SQL source again and again in a loop is not a best practice, just fetch data once and use Resident.

Not applicable
Author

Marcus, I used the debugger mode. Both the loops are working correctly. Outer loop selects a value of id and dcode and then the inner loop used that value to load the count for unique combination of various dimensions. But when I see the results, I always see only one dcode corresponding to each ID.

Also, I want the results for each ID, dcode combination combined in a single qvd. 

Not applicable
Author

Shubham, If the store statement is outside the loop, it will pick just the last value of ID, dcode and store in the qvd. I am not sure, how I can use RESIDENT load here.

marcus_sommer

Your loop should have taken 5 iterations - was there 5 times an increasing of records within your table? Take a look within the log-file or run the script again. And aren't there each time more records it meant that not all combinations of ID and dcode are within your main_data-table.

- Marcus

Not applicable
Author

Yes, That’s exactly what I am seeing. I get 17 combinations of ID and dcode (which are based on a query on the maintable, so there are no cases which will not exists). Then for loop runs 17 times, each time loading a different number of records. My final count also matches the SQL query but when I create the dashboard, I see only 1 dcode for each ID. My guess is that I am missing something about qvd storage here or the fact that, the store statement is being over written. But then, the matching final count is perplexing me.

-Akshata

marcus_sommer

How do you check your final data - with listboxes or both fields with a tablebox?

- Marcus

Not applicable
Author

both fields with a tablebox

marcus_sommer

Are there any selections applied? Have you tried to load this qvd in a new empty application?

- Marcus