Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

pushpita
Contributor

interval between date and time

Hi All,

I have a requirement like .. I have 2 files with IP, UID and User Time and i need to identify the common User name having same IP, UID and User Time falling with in Initial and Final Time of 2 nd file.

File 1 :

Ip                         UID          User Time

142.09.80.30, a00asdtt, [10/Sep/2016:10:34:00 +0800]

File 2:

IP                              UID     intial time                                   Final Time                                        UserNAme

122.10.90.104,E00543,   Tue Sep 13 10:44:37 SGT 2016Tue Sep 13 11:44:25 SGT 201         CV001

Message was edited by: Priyabrata Das

1 Solution

Accepted Solutions
MVP
MVP

Re: interval between date and time

T2:

Load

  UID,

  IP, 

  UID & IP as Key,

  TimeStamp(TimeStamp#(Trim(Mid(Replace(Time,' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as Time

Inline

[

  UID, IP, Time

  A00123, 170.16.52.294, Tue Sep 13 14:51:10 SGT 2016

  A00124, 170.16.52.295, Tue Sep 13 10:51:10 SGT 2016

];

T1:

Load

  UID,

  IP,

  UID & IP as Key,

  TimeStamp(TimeStamp#(Trim(Mid(Replace([Start Id],' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as StartID,

  TimeStamp(TimeStamp#(Trim(Mid(Replace([Final Id],' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as FinalID

Inline

[

  UID, IP, Start Id, Final Id

  A00123, 170.16.52.294, Tue Sep 13 10:50:09 SGT 2016, Tue Sep 13 10:50:09 SGT 2016

  A00124, 170.16.52.295, Tue Sep 13 10:50:09 SGT 2016, Tue Sep 13 10:56:09 SGT 2016

];

Inner Join

IntervalMatch(Time, Key)

Load StartID, FinalID, Key Resident T1;

Left Join (T2)

Load * Resident T1;

Drop Table T1;

9 Replies
qlikviewchintan
Contributor

Re: interval between date and time

Dear Priyabarta

You may try

LOad Ip, UID, User Time from tablename(File1);

Load IP, UID, initial time, Final Time from tablename(File2);

Interval match (User Time)

Load initial time, Final Time from tablename2(File2);

Hope this helps you.

Thanks & regards

Chintan Gala

MVP
MVP

Re: interval between date and time

You need an interval match like:

Data:

LOAD

  IP,

  UID,

  [User Time]

FROM <fact source>;

Left Join (Data)

InterValMatch([UserTime], IP, UID)

LOAD [Initial Time], [Final Time], IP, UID

FROM <interval source>;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
hemachandran
Contributor

Re: interval between date and time

Hi,

Try this code

=Intervalmatch(Ip,UID,intial time,Final Time)

Ip,

UID,

intial time,

Final Time

RESIDENT TABLENAME

singhpooja
Contributor III

Re: interval between date and time

File1:

LOAD

  IP,

  UID,

  [User Time]

FROM <File1_Path>;

Left Join (File1)

InterValMatch([UserTime], IP, UID)

LOAD [Initial Time], [Final Time], IP, UID

FROM <File2_Path>;

mohammadkhatimi
Valued Contributor

Re: interval between date and time

PFA...May this will helps u...!!

Regards,

Mohammad

pushpita
Contributor

Re: interval between date and time

Hi Khatimiti,

If you check the data model of ur app the initial and Final dates are getting generated.

pushpita
Contributor

Re: interval between date and time

Hi Jonathan,

I tried the logic advised by you but its not working . i have added the sample file,please help in the interval match.

MVP
MVP

Re: interval between date and time

T2:

Load

  UID,

  IP, 

  UID & IP as Key,

  TimeStamp(TimeStamp#(Trim(Mid(Replace(Time,' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as Time

Inline

[

  UID, IP, Time

  A00123, 170.16.52.294, Tue Sep 13 14:51:10 SGT 2016

  A00124, 170.16.52.295, Tue Sep 13 10:51:10 SGT 2016

];

T1:

Load

  UID,

  IP,

  UID & IP as Key,

  TimeStamp(TimeStamp#(Trim(Mid(Replace([Start Id],' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as StartID,

  TimeStamp(TimeStamp#(Trim(Mid(Replace([Final Id],' SGT',''),5)),'MMM DD hh:mm:ss YYYY')) as FinalID

Inline

[

  UID, IP, Start Id, Final Id

  A00123, 170.16.52.294, Tue Sep 13 10:50:09 SGT 2016, Tue Sep 13 10:50:09 SGT 2016

  A00124, 170.16.52.295, Tue Sep 13 10:50:09 SGT 2016, Tue Sep 13 10:56:09 SGT 2016

];

Inner Join

IntervalMatch(Time, Key)

Load StartID, FinalID, Key Resident T1;

Left Join (T2)

Load * Resident T1;

Drop Table T1;

pushpita
Contributor

Re: interval between date and time

Hi Manish,

The Time Format for T1 table is different i.e. [13/Sep/2016:10:44:00 +0800]