4 Replies Latest reply: Mar 27, 2013 5:45 PM by brentski RSS

    Summarize and Alter Value during load

      I am new to using Qlickview and have so far found the tool to be very useful.

       

      I am looking for help on taking data during the loading process and determining if it meets certain conditions and then modifying what is actually loaded.

       

      In Excel, I use an array formula to accomplish the task but was hoping to automate the process more in Qlickview.

       

      Data

       

      Person     Measure     Compliant

      1               CIS-dtap     1

      2               Mammo       0

      1               CIS-MMR    1

      3               Colo            1

      1                CIS-VZV    1

      n               CIS-dtap     0

      n               cis-mmr       1

      n               cis-vzv          1

       

      I need to evaluate where CIS-dtap and CIS-MMR  which converts to CIS-COMBO and the other CIS values go away. Then load final data from above to show:

       

      1               CIS-COMBO     1

      2               Mammo             0

      3               Colo                   1

      n               CIS-COMBO     0

       

      My thoughts are that I need to load a temp table which would index Person and Measure and only load where CIS* exists.  The next step is where I get stuck.

       

      How do I compare acrossed rows and make the determination for the same person evaluate the outcome?

       

      Just using logic, I would say:

      for each person

      CIS-dtap * compliant + CIS-mmr*compliant; if> 1  then the resultant record CIS-COMBO =1 else CIS-COMBO=0

       

      Any thoughts or direction would be appreciated on getting me started on the evaluation between records.  I have simplified the example, I actually have 10 vaccines that I am looking across and I need to have 5 specific vaccinations but I hope the concept is the same.