If-Statement in a table dimension cannot filter over magnifying glass

[Applicable88]

Hello,

I have two date times. One is Inbound and the other one outbound. So the logic is, if the inbound time exist and there is also a outbound time, i know the material is not anymore in the inventory:

if(inboundtime>0 and outboundtime>0, 'Given out on" & [Outboundtime]

As you can see, instead of showing me only the time, there is the information 'given out on' included. 

Now the next condition is when there is a inboundtime and NO outboundtime (outboundtime is Null), I know for sure the material is still in my inventory. So put altogether I have this:

if([inboundtime]>0 and isNull([outboundtime]),'in inventory',
if([inboundtime]>0 and [ountboundtime]>0 , 'given out on ' & [outboundtime]))

So far as a simple dimension it works pretty well. I have many already given out fields which I can chose exactly the date and qlik is filtering accordingly. 

But when I chose the field 'in Inventory' which exist in the search only one time, then I get a confused mix of results and not only those which are still in inventory. I think the if-statement cannot use this way maybe. Anyone has a tip for me?

Thank you in advance.

Best. 

[jyothish8807]

Hi,

Try creating this field in the script and use the field as dimension directly. You will get the right values in serches then.

Load

*,

if([inboundtime]>0 and isNull([outboundtime]),'in inventory',
if([inboundtime]>0 and [ountboundtime]>0 , 'given out on ' & [outboundtime]))  as NewDimension

from <>;

Br,
KC

[Applicable88]

@jyothish8807 , thanks for the quick reply. But that way its not going to work. The problem is the inboundtime and the outboundtime are coming from two different sources. So when you put the load script into one of the source it either will say it cannot find inboundtime or cannot find outboundtime. Depending if I put the script to the source of inboundtime or either outboundtime. 

Is there a another way? 

[jyothish8807 · Accepted solution]

The challenge here i believe is, your calculated dimension the custom value do not have proper association with complete data model. 

Can you try this expression once:

if(len(trim([inboundtime])) > 0 and len(trim([outboundtime]))=0,'in inventory',
if(len(trim([inboundtime])) >0 andlen(trim([outboundtime])) >0 , 'given out on ' & [outboundtime]))