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.
Data_Sample for qlik2..csv 12.4 MB
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..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
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)
If(Min(Status='IN_NETWORK'),'IN NETWORK','OUT OF NETWORK') as [out
On Tue, Sep 20, 2016 at 6:33 PM, Marco Wedel <email@example.com>