16 Replies Latest reply: Dec 13, 2016 9:17 AM by akshata tare RSS

    for loop load issue

    akshata tare

      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

        • Re: for loop load issue
          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

            • Re: for loop load issue
              akshata tare

              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
              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.

              • Re: for loop load issue
                akshata tare

                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!