3 Replies Latest reply: Jan 13, 2012 7:40 PM by Miguel Angel Baeyens de Arce RSS

    if Statement

      Sorry for the length of this post. I am trying to create a view to look at active response plan information for my fire department. I have an ODBC connection to our Computer Aided Dispatch (CAD) system.

      Each fire truck has a resource and/or a capability attached to it that CAD uses to pick the correct truck to send to a particular call type. There is a capability table and a resource table. My issue is that duplicate SQL IDs exist between the two tables and so I get data listed incorrectly in my view, for example

      AlarmLevel

      PrimaryOrder

      ResourceDescription

      CapabilitiesDescription

      PrimaryCode

      1

      1

      ENG04-Engine ESD04

      MED00 - COA (C)

      0

       

      It should look like this,

       

      AlarmLevel

      PrimaryOrder

      ResourceDescription

      CapabilitiesDescription

      PrimaryCode

      1

      1

      MED00 - COA (C)

      0

       

      I have been trying to construct and “if” statement where if(PrimaryCode=0, Resource, ‘’). I am not sure if I am placing it in the wrong place in the script or if the statement is just incorrectly built. Everything I have tried so far results in an error.

       

      I would then need to add a second statement where if(PrimaryCode=1, Capability, ‘’) or would look like this,

      AlarmLevel

      PrimaryOrder

      ResourceDescription

      CapabilitiesDescription

      PrimaryCode

      1

      1

      LAD00-Ladder AFD

       

      1

       

      If it was all perfect then my view would look like this when I had a mixed response plan,

       

      AlarmLevel

      PrimaryOrder

      ResourceDescription

      CapabilitiesDescription

      PrimaryCode

      2

      1

       

      LAD01 (C)

      0

      2

      2

       

      ENG13 (C)

      0

      2

      3

       

      ENG01 (C)

      0

      2

      4

      LAD00-Ladder AFD

       

      1

      2

      5

      ENG00-Engine AFD

       

      1

      2

      6

      ENG00-Engine AFD

       

      1

      2

      7

      ENG00-Engine AFD

       

      1

      2

      8

      BC00-Command AFD

       

      1

      2

      9

      DC00-Command, AFD

       

      1

       

      Below I show how I am linking it all together. It may be that I am not doing it the best way but with my limited SQL knowledge I am doing what I can figure out.

       

       

      ResponsePlanList:SQL SELECT
             ID as ResponsePlanList_ID,
          PlanID,
          PrimaryCode,
          PrimaryID as PrimaryID1,
          PrimaryID as PrimaryID2,
          PrimaryOrder
      FROM "Production_Lookups".dbo."RESPONSEPLANLIST";

       

      Resource:SQL SELECT
             ID as PrimaryID1,
          Code as ResourceCode,
          Description as ResourceDescription,
          TravelTimeWeight
      FROM "Production_Lookups".dbo."RESOURCE"
      WHERE AgencyTypeID=2;

       

      Capabilities:SQL SELECT
             ID as PrimaryID2,
          Code as CapabilitiesCode,
          Description as CapabilitiesDescription
      FROM "Production_Lookups".dbo."CAPABILITIES"
      WHERE AgencyTypeID=2;

       

       

      Thanks for your help,

       

      William

        • Re: if Statement
          Miguel Angel Baeyens de Arce

          Hello William,

           

          Here is what I'd do to get one table of data with all required fields. Now you should have only one bigger table with all data joined by the fields according to your renaming. The "LOAD *" part is very useful and powerful in QlikView, and I strongly recommend you to use it. It's described in the Reference Manual, it's not required but it has a lot of advantages, as you can read in this thread and this other thread.

           

          ResponsePlanList:
          LOAD *;
          SQL SELECT
              ID as ResponsePlanList_ID,
              PlanID,
              PrimaryCode,
              PrimaryID as PrimaryID1,
              PrimaryID as PrimaryID2,
              PrimaryOrder
          FROM "Production_Lookups".dbo."RESPONSEPLANLIST";
          
          Resource:
          LEFT JOIN (ResponsePlanList) LOAD *;
          SQL SELECT
              ID as PrimaryID1,
              Code as ResourceCode,
              Description as ResourceDescription,
              TravelTimeWeight
          FROM "Production_Lookups".dbo."RESOURCE"
          WHERE AgencyTypeID=2;
          
          Capabilities:
          LEFT JOIN (ResponsePlanList) LOAD *;
          SQL SELECT
              ID as PrimaryID2,
              Code as CapabilitiesCode,
              Description as CapabilitiesDescription
          FROM "Production_Lookups".dbo."CAPABILITIES"
          WHERE AgencyTypeID=2;
          

           

          Hope that returns the results as expected.

           

          Miguel

            • Re: if Statement

              Miguel,

               

              I tried what you suggested and have been reading about the LOAD command, JOIN etc. It has been very helpful but didn't do what I needed. I have added the LOAD command to my script however based on your advice.

               

              Our CAD system uses the PrimaryCode field to determine if a vehilce should be picked based on Capability or Resource. If the PrimaryCode is "0" then CAD is looking for Capability. If the PrimaryCode is "1" then the unit is picked by Resource.

               

              Because the Resource and Capability tables each start at 1 and count up I have some Resources and some Capabilities that have common SQL ID. This causes my table to show both a Resource and a Capability when I only want one of them displayed based on the PrimaryCode number.

               

              PrimaryOrder

              ResourceDescription

              CapabilitiesDescription

              PrimaryCode

              1

              ENG04-Engine ESD04

              MED00 - COA (C)

              0

               

              ENG04 and MED00 have the same SQL id however only the MED00 should display since the PrimaryCode is "0". So what I have been trying to do is make the ResourceDescription field return a null value if the PrimaryCode is "0".

               

              To help with the firefighter terminology:

               

              Resource = Ladder Truck, Pumper Truck (Engine), Rescue Unit (the type of toolbox)

              Capability = pump, defibrillator, hose, gas monitor, rescue tools (the type of tools)

               

              Thanks,

                • Re: if Statement
                  Miguel Angel Baeyens de Arce

                  Thanks for the clarification William, it does help understanding the data.

                   

                  You can set in the charts a very powerful feature of QlikView, that is Set Analysis, that, basically, allows you to display in a chart a limited set of records based on values for fields. I don't know if the following example will fit your needs, but using your script as it is (JOINed tables), you can create a new chart object, type straight table, with Primary Order as the dimension, and an expression to display CapabilitiesDescription similar to this

                   

                  Only({< PrimaryCode = {0} >} CapabilitesDescription)
                  

                   

                  And using the same approach to the ResourceDescription

                   

                  Only({< PrimaryCode = {1} >} ResourceDescription)
                  

                   

                  Or maybe a more complex SQL statement that does the JOIN with conditions might do, but without better knowledge of your actual data, it's hard to say. But here goes my attempt. You will see no JOINs this time, but the idea is as follows:

                  • Load the table ResponsePlanList that have the PrimaryID in common with Capabilities and Resources.
                  • Since you only want to load those Resources where the corresponding value in PrimaryCode is 1, that is in a different table, we create that mapping table that will return, by means of the ApplyMap() function in the second table the value of PrimaryCode corresponding to that PrimaryID.
                  • And likewise with the Capabilities, that will only loaded if the ResponsePlanList has a PrimaryCode equal to 0 for the Capability PrimaryID

                   

                  ResponsePlanList:
                  LOAD *;
                  SQL SELECT
                      ID as ResponsePlanList_ID,
                      PlanID,
                      PrimaryCode,
                      PrimaryID,
                      PrimaryOrder
                  FROM "Production_Lookups".dbo."RESPONSEPLANLIST";
                  
                  ResourceIDPrimaryCodeMap:
                  MAPPING LOAD PrimaryID,
                       PrimaryCode
                  RESIDENT ResponsePlanList;
                  
                  Resource:
                  LOAD *
                  WHERE ApplyMap('ResourceIDPrimaryCodeMap', PrimaryID) = 1;
                  SQL SELECT
                      ID as PrimaryID,
                      Code as ResourceCode,
                      Description as ResourceDescription,
                      TravelTimeWeight
                  FROM "Production_Lookups".dbo."RESOURCE"
                  WHERE AgencyTypeID=2;
                  
                  Capabilities:
                  LOAD *
                  WHERE ApplyMap('ResourceIDPrimaryCodeMap', PrimaryID) = 0;
                  SQL SELECT
                      ID as PrimaryID,
                      Code as CapabilitiesCode,
                      Description as CapabilitiesDescription
                  FROM "Production_Lookups".dbo."CAPABILITIES"
                  WHERE AgencyTypeID=2; 
                  

                   

                  Or using plain SQL to get all in one table (I'm guessing and I hope not have messed the PrimaryCode for Resources and Capabilites) and probably simpler:

                   

                  ResponsePlanList:
                  LOAD ResponsePlanList_ID,
                       PlanID,
                       PrimaryCode,
                       PrimaryID,
                       PrimaryOrder,
                       ResourceCode,
                       ResourceDescription,
                       TravelTimeWeight,
                       CapabilitesCode,
                       CapabilitiesDescription;
                  SQL SELECT
                      RPL.ID AS ResponsePlanList_ID,
                      RPL.PlanID,
                      RPL.PrimaryCode,
                      RPL.PrimaryID,
                      RPL.PrimaryOrder,
                      Res.Code AS ResourceCode,
                      Res.Description AS ResourceDescription
                      Res.TravelTimeWeight,
                      Cap.Code AS CapabilitiesCode,
                      Cap.Description AS CapabilitiesDescription
                  FROM "Production_Lookups".dbo."RESPONSEPLANLIST" AS RPL
                  LEFT JOIN "Production_Lookups".dbo."RESOURCE" AS Res ON (RPL.ID = Res.ID AND RPL.PrimaryCode = 1 AND Res.AgencyTypeID = 2)
                  LEFT JOIN "Production_Lookups".dbo."CAPABILITIES" AS Cap ON (RPL.ID = Cap.ID AND RPL.PrimaryCode = 0 AND Cap.AgencyTypeID = 2);
                  

                   

                  I know this is a lot of information for the first time and probably not the best way to get what you are looking for, but I hope that makes sense.

                   

                  Miguel

                   

                  By the way, my time to a lenghty post!