Selecting a value based on a time selection

[lisab80]

Hi

Currently I am working on a qlikview app that reads in an agency name and then maps their status as either C or NC

MapAgency:
Mapping
LOAD AgencyName, Status
FROM ...Agency.xlsx

and then in my load statement 

APPLYMAP('MapAgency',[Source],null()) as [Agency Type]

I have now been asked to develop further where based on a date an agency can change status.

For example in spreadsheet attached Agency1 will always be C as the date field is empty so it keeps its original status, whereas Agency2 starts of as NC and then from 1st Feb 23 the status will be shown as C so any historic data before 01/02/2023 for Agency2 will be shown as NC and any data after this date will be shown as C.

The logic should cover every AgencyName should be assigned their status from column B initially and then need to do a check is todays date > Date (column c) if yes then Status becomes New Status if no or date is blank then Status does not change

Any help greatly appreciated

[joaopaulo_delco]

Hi @lisab80 !

       Try the following condition:

if(not IsNull(DateField),
	if(DateField<Today(2),
	    if(Status='NC','C','NC'),Status)
,Null()) as NewStatus,

[lisab80]

Hi Joaopaulo_delco

Not quite sure how that condition will fit into my code because the date field needs to come from the excel sheet attached and the mapping table only reads in AgencyName and Status from the excel sheet attached.

As the mapping tables can only have 2 columns how do I change my code so I now read in AgencyName, Status, Date and New Status?

Thanks

[joaopaulo_delco]

You can do a "preceding load" after you use the ApplyMap function. 

Something like this:

MapAgency:
Mapping
LOAD AgencyName, Status
FROM ...Agency.xlsx

Load
AgencyName,
Status,
Date,
if(not IsNull(Date),
	if(Date<Today(2),
	    if(Status='NC','C','NC'),Status)
,Null()) as NewStatus;
Load 
AgencyName,
APPLYMAP('MapAgency',AgencyName,null()) as Status,
Date
From [lib://path/example.xlsx]...;

[lisab80]

I wanted to clarify something to make it clearer on where i am reading my data from

My load statement that uses the applymap function is reading from a sql view

Load 

Fields

APPLYMAP('MapAgency',[Source],null()) as [Agency Type]

FROM sql view

The Source value mentioned in the apply map statement is the Agency Name from my sql view and this then goes to the mapping table and finds the agency name and then returns either NC or C and stores this as [Agency Type]. I have used an excel mapping table as the system that I am reading the data from only stores the AgencyName.

The mapping table should read from the example.xlsx file

MapAgency:
Mapping
LOAD AgencyName, Status
FROM ...example.xlsx

Would the suggestion above work as i am still trying to figure out how to implement

Thanks

[joaopaulo_delco]

@lisab80 

The kind of source (xlsx, sql, txt) doesn't matter too much.

Check this out:

MapAgency:
Mapping
LOAD AgencyName, Status
FROM ...Agency.xlsx

LIB CONNECT TO 'Your string connection';

[TableName]:
Load
AgencyName,
Status,
Date,
if(not IsNull(Date),
	if(Date<Today(2),
	    if(Status='NC','C','NC'),Status)
,Null()) as NewStatus;
Load 
AgencyName,
APPLYMAP('MapAgency',AgencyName,null()) as Status,
Date;
SQL Select 
    AgencyName,
    Date
From YourTable;

[lisab80]

Hi

Thanks for clarifying and it makes sense what you are trying to do my getting the first status then using the date element to overwrite the Status and save as NewStatus.

I have 1 issue though the Date field is not coming from the SQL view it is coming from the excel file that is used in the MapAgency mapping table.

Thanks

[joaopaulo_delco · Accepted solution]

MapAgency:
Mapping
LOAD AgencyName, Status
FROM ...Agency.xlsx

MapDate:
Mapping
LOAD AgencyName, Date
FROM ...Agency.xlsx

LIB CONNECT TO 'Your string connection';

[TableName]:
Load
AgencyName,
Status,
Date,
if(not IsNull(Date),
	if(Date<Today(2),
	    if(Status='NC','C','NC'),Status)
,Null()) as NewStatus;
Load 
AgencyName,
ApplyMap('MapAgency', AgencyName,null()) as Status,
ApplyMap('MapDate', AgencyName, null())  as Date;
SQL Select 
    AgencyName
From YourTable;

[lisab80]

I have implemented the above solution

LOAD *,

if(not IsNull(Date), if(Date<Today(2),if(Status='NC','C','NC'),Status) ,Null()) as NewStatus;

LOAD

other fields,

APPLYMAP('MapAgency',AgencyName,null()) as Status,
APPLYMAP('MapDate', AgencyName, null()) as Date;

from SQL

I only applied 1 date for testing to Agency4 with a date of 01/05/2023 (1st may 23).  All other AgencyNames had a blank date so they should keep their original status.

Agency4 starts of as C and after 01/05/2023 then it should be shown as NC when I loaded the app this agency is showing as NC irrespective of the date (showing as NC on 01/01/2022 and NC on 02/05/2023) plus all other agency names irrespective of date have the NewStatus value of blank (or - as shown in qlikview)

[lisab80]

Ignore my previous comment as I have figured out why all the other agencies were showing as blank

I have updated the code to remove the last NULL() as this was setting all other agency names to blank if their Date value was blank

LOAD *,

if(not IsNull(Date), if(Date<ShiftDate,if(Status='NC','C','NC'),Status) ,Status) as NewStatus;

LOAD

other fields,

APPLYMAP('MapAgency',AgencyName,null()) as Status,
APPLYMAP('MapDate', AgencyName, null()) as Date;

from SQL

In regards to Agency4 always showing as NC irrespective of the Date this is because using Today will change the status based on the day you load the app not when the shift in the Agency actually happened therefore changing all historic data.  I have updated this to now look at ShiftDate and everything is looking good so far.

I will run a few more tests and let you know - Thanks