Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
julietotsch
Contributor II
Contributor II

If Statement - Can I use IN

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')

 

Labels (3)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

4 Replies
Jobson_joseph
Contributor II
Contributor II

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.

MarcoWedel

 

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')))

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

julietotsch
Contributor II
Contributor II
Author

Thanks for this - it was a huge help!