Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Hi @lisab80 !
Try the following condition:
if(not IsNull(DateField),
if(DateField<Today(2),
if(Status='NC','C','NC'),Status)
,Null()) as NewStatus,
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
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]...;
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
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;
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
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;
I have implemented the above solution
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