topic Re: for loop load issue in Connectivity & Data Prep

for loop load issue

Thu, 08 Dec 2016 22:43:05 GMT

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

Re: for loop load issue

marcus_sommer — Fri, 09 Dec 2016 09:41:23 GMT

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

Re: for loop load issue

shubham_singh — Fri, 09 Dec 2016 10:21:59 GMT

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.

Re: for loop load issue

Fri, 09 Dec 2016 15:36:15 GMT

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.

Re: for loop load issue

Fri, 09 Dec 2016 15:38:05 GMT

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.

Re: for loop load issue

marcus_sommer — Fri, 09 Dec 2016 16:05:17 GMT

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

Re: for loop load issue

Fri, 09 Dec 2016 16:17:30 GMT

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

Re: for loop load issue

marcus_sommer — Fri, 09 Dec 2016 16:20:57 GMT

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

- Marcus

Re: for loop load issue

Fri, 09 Dec 2016 16:23:00 GMT

both fields with a tablebox

Re: for loop load issue

marcus_sommer — Fri, 09 Dec 2016 16:35:30 GMT

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

- Marcus

Re: for loop load issue

Fri, 09 Dec 2016 16:37:36 GMT

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?

Re: for loop load issue

marcus_sommer — Fri, 09 Dec 2016 16:45:38 GMT

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

Re: for loop load issue

shubham_singh — Sat, 10 Dec 2016 07:21:50 GMT

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.

Re: for loop load issue

Mon, 12 Dec 2016 20:38:24 GMT

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!

Re: for loop load issue

Mon, 12 Dec 2016 21:20:51 GMT

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.

Re: for loop load issue

marcus_sommer — Tue, 13 Dec 2016 05:38:18 GMT

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

Re: for loop load issue

Tue, 13 Dec 2016 14:17:28 GMT

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