If with multiple conditions

[Anonymous]

Hi Qlik Exprests,

I have time based data that shows status of each device in the region. In summary data consists of date and time or time stamp, device name, status and region. Let's assume thee are 4 regions. The status can be in network or out of network

Unfortunately, at a time stamp the same device can be reported as in network in one region (with the duration of in network up to that point) and out of network in other regions. So, at every report interval the same terminal can be reported twice or 3 times with different status from different region. 

I'd like to filter the status for the real out of network only when that terminal is reported out of network for at least 2 regions.

So, this will be a combination of multiple if for each region.  For instance, for region 1, if the status itself and and another region (could be region 2, 3 or 4) is out of network then it is considered out of network. For region 2, then it will need to check if one of the regions 1,3 and 4 out of network and it is considered out of network and so on.

I will need to categorize it in the script.

What would be the right statement for this?

Thanks for help

[swuehl]

Could you post some sample data and your requested result?

[Anonymous]

Hi Stefan,

Please find attached the sample file. The date and time is in separate column and I need to make it a combined time stamp for every 5 minutes. What I did in the loading script is

Timestamp(Floor( Timestamp(tpa_date & ' ' & tpa_time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],

What I expect to see is another field called out of network that filters the condition where each device is reported "undetected" in more than one region instead of being reported with different status in different region.  In other words, if the same device is reported as "in network" in the same reporting interval then it is considered as in network. Only when that device is NOT reported as "in network"  then it is considered out of network.

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

tabDevNet:
LOAD RecNo() as RecNo,
    Date,
    Time,
    Timestamp(Date+Time) as DateTime,
    Timestamp(Floor(Timestamp(Date&' '&Time,'DD/MM/YYYY hh:mm:ss'),1/288)) as [Time Stamp],
    [network id],
    [area number],
    customer_id,
    type,
    Status as StatusDuration,
    TextBetween(Status,'(',' ')  as Status,
    region
FROM [https://community.qlik.com/servlet/JiveServlet/download/1127307-246250/Data_Sample%20for%20qlik2..cs...] (txt, codepage is 1252, embedded labels, delimiter is ',', msq)
Where [network id];

Left Join (tabDevNet)
LOAD customer_id,
    [Time Stamp],
    If(Min(Status='IN_NETWORK'),'IN NETWORK','OUT OF NETWORK') as [out of network]
Resident tabDevNet
Group By customer_id, [Time Stamp];

hope this helps

regards

Marco

[Anonymous]

Hi Marco,

Thanks. I am still using personal edition and working on the license,

unfortunately can't open the file.

Tried to replicate the script but still unable to successfully run it as

filed in the second table was not found. Where does the source of the

second table come from?

of Left Join (tabDevNet)

LOAD customer_id,

,

If(Min(Status='IN_NETWORK'),'IN NETWORK','OUT OF NETWORK') as [out

of network]

On Tue, Sep 20, 2016 at 6:33 PM, Marco Wedel <qcwebmaster@qlikview.com>

[swuehl]

It's the label of the first table in Marco's script:

tabDevNet: 

LOAD RecNo() as RecNo,

     ...

[Anonymous]

OK, thanks!