if Statement

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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,

[Miguel_Angel_Baeyens]

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!