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

[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]

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.

[#4]

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. 

[#5]

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

[#7]

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

[#9]

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