Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have heatmaps for the number of items that are in each unit. For some months, there are no items and for others, the unit was not in existence during that month. Prior to the last upgrade, I had zeroes in the units for no items and blank for units that had not existed. Now, any time there are zero items, the heat map shows blanks.
To resolve this, I am creating zero data for the months that do not have items. This resolves the issue of the zeroes. Unfortunately, this means that the units that didn't exist will have zeroes in months where it should be blank. To resolve this, I am adding a table to my load script that will include the start date for the units. I want to use an if statement, as I have many units with the same start Month Year, a few units with unique start Month Year, and the majority of units with start dates the same as the start dates of the data.
I am not sure how to write the if statement. Below is what I want in pseudocode:
If unit in (UnitA, UnitB, UnitC, UnitD) then, '01/01/2022', else if unit = UnitE, then '04/01/2020', else if unit = UnitF, '08/01/2021', else '01/01/2019')
Another approach is to use a mapping table.
UnitMap:
Mapping Load * Inline [
unit, startdate
UnitA, 01/01/2022
UnitB, 01/01/2022
...
UnitE, 04/01/2020
];
In your Load statement fields:
ApplyMap('UnitMap', unit, Date(01/01/2019)) as UnitStartDate
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
hi
may be try something similar below;
if(Pick(Match(unita,unitb,unitc,unitd),unit),'01/01/2022')
its incomplete, you can try this way.
If(Match(unit,UnitA,UnitB,UnitC,UnitD), '01/01/2022',
If(unit=UnitE, '04/01/2020',
If(unit=UnitF, '08/01/2021', '01/01/2019')))
Another approach is to use a mapping table.
UnitMap:
Mapping Load * Inline [
unit, startdate
UnitA, 01/01/2022
UnitB, 01/01/2022
...
UnitE, 04/01/2020
];
In your Load statement fields:
ApplyMap('UnitMap', unit, Date(01/01/2019)) as UnitStartDate
-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com
Thanks for this - it was a huge help!