topic Re: Selecting a value based on a time selection in QlikView

Selecting a value based on a time selection

lisab80 — Thu, 13 Jul 2023 16:35:56 GMT

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

Re: Selecting a value based on a time selection

joaopaulo_delco — Thu, 13 Jul 2023 17:12:19 GMT

Hi @lisab80 !

Try the following condition:

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

Re: Selecting a value based on a time selection

lisab80 — Fri, 14 Jul 2023 09:40:29 GMT

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

Re: Selecting a value based on a time selection

joaopaulo_delco — Fri, 14 Jul 2023 12:09:46 GMT

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

Re: Selecting a value based on a time selection

lisab80 — Fri, 14 Jul 2023 14:13:36 GMT

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

Re: Selecting a value based on a time selection

joaopaulo_delco — Fri, 14 Jul 2023 18:26:28 GMT

@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;

Re: Selecting a value based on a time selection

lisab80 — Mon, 17 Jul 2023 14:20:03 GMT

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

Re: Selecting a value based on a time selection

joaopaulo_delco — Mon, 17 Jul 2023 17:11:03 GMT

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;

Re: Selecting a value based on a time selection

lisab80 — Wed, 19 Jul 2023 15:36:11 GMT

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)

Re: Selecting a value based on a time selection

lisab80 — Wed, 19 Jul 2023 16:56:01 GMT

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

Re: Selecting a value based on a time selection

lisab80 — Thu, 20 Jul 2023 17:04:49 GMT

Thanks for the great solution it worked a treat