6 Replies Latest reply: Sep 21, 2016 10:08 AM by Fikri Shihab RSS

    If with multiple conditions

    Fikri Shihab

      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

        • Re: If with multiple conditions
          Stefan Wühl

          Could you post some sample data and your requested result?

            • Re: If with multiple conditions
              Fikri Shihab

              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.

                • Re: If with multiple conditions
                  Marco Wedel

                  Hi,

                   

                  maybe one solution could be:

                   

                  QlikCommunity_Thread_233455_Pic2.JPG

                  QlikCommunity_Thread_233455_Pic3.JPG

                  QlikCommunity_Thread_233455_Pic1.JPG

                   

                   

                  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..csv] (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