Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ravikumar_iyana
Contributor

How to compare the rows with in One Column and Substract the time stamp

Hi All,

I have to compare the values present in two rows in Columns only.

STOREID Date TimeSalesResult
A/F363002.05.2018 05:07:24525525
A/F363505.05.2018 09:10:29101101
A/F363505.05.2018 15:16:24123123
A/F363505.05.2018  16:52:29123
A/F363811.05.2018 19:24:28565565
A/F363912.05.2018 18:26:25141141

Here the STOREID and DateTime and Sales are 3 fields. When the transaction is happened with in the 2 hours it should be consider as only one transaction.  For above example: STORE ID A/F3635 has done 3 transactions. There are 2 transctions are happend with in the 2 hours. so i considered as only One sales, i.e., 123.

Please find below sample attachment file.

Thanks in advance.

1 Solution

Accepted Solutions
jyothish8807
Honored Contributor II

Re: How to compare the rows with in One Column and Substract the time stamp

This is working for me:

LOAD [Store ID],

     Sales,

     date([Ac.GI date],'MM/DD/YY') as [Ac.GI date],

     time(PGI_Time,'hh:mm:ss') as PGI_Time ,

     interval(timestamp(timestamp#(date([Ac.GI date],'MM/DD/YY')&' '&time(PGI_Time,'hh:mm:ss'),'MM/DD/YY hh:mm:ss'),'MM/DD/YY hh:mm:ss') -

     previous(timestamp(timestamp#(date([Ac.GI date],'MM/DD/YY')&' '&time(PGI_Time,'hh:mm:ss'),'MM/DD/YY hh:mm:ss'),'MM/DD/YY hh:mm:ss')),'h') as New

FROM

(ooxml, embedded labels, table is [Multiple Logic Building], filters(

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5))

));

Br,

KC

Best Regards,
KC

View solution in original post

14 Replies
rkpatelqlikview
Contributor III

Re: How to compare the rows with in One Column and Substract the time stamp

Hello Ravi,

Can you please provide sample data? It will be appreciated

jyothish8807
Honored Contributor II

Re: How to compare the rows with in One Column and Substract the time stamp

Hi Ravi,

Pfa, Hope this is what you are looking for.

Br,

KC

Best Regards,
KC
rkpatelqlikview
Contributor III

Re: How to compare the rows with in One Column and Substract the time stamp

Hi Jyothish,

Can you please suggest on Int column. when the Date and time is seperate.

As per the Ravi Source data, The below script is supporting for to generate Int column. If there is seperate column for Date and Time.

Like below i tried.

Load

timestamp#(Ac.GI date &' '& Time(PGI_Time) ,'DD/MM/YY hh:mm:ss') as DateTime,

   Store ID,

Time(PGI_Time) as Time,

     Sales,

     Plant,

     Ac.GI date,

   //  Interval( Timestamp#(Ac.GI date,'MM.DD.YYYY hh:mm:ss')-Previous(Timestamp#(Ac.GI date,'MM.DD.YYYY //hh:mm:ss')),'h') as Int,

   Interval( timestamp#(Ac.GI date, &' '&Time(PGI_Time) ,'DD/MM/YY hh:mm:ss')-Previous(timestamp#(Ac.GI date&' '& Time(PGI_Time) ,'DD/MM/YY hh:mm:ss')),'hh:mm:ss') as Int,

if(STOREID=Previous(STOREID) and Interval(Time#([Date Time],'M.D.YYYY h:mm:ss')-Previous(Time#([Date Time],'M.D.YYYY h:mm:ss')),'h')<='2',Sales,'') as New

FROM
[https://community.qlik.com/thread/301146]
(
html, codepage is 1252, embedded labels, table is @1, filters(
Remove(Col, Pos(Top, 4))
));

jyothish8807
Honored Contributor II

Re: How to compare the rows with in One Column and Substract the time stamp

Hi Kumar,

Yes, if the Date and time are two different field , then you can combine them and make a proper date.

The ides is to have the Field in proper timestamp format so Qlikview can understand it.

The below should work, the logic goes the same.

timestamp#(date(Ac.GI date,'DD/MM/YY') &' '& Time(PGI_Time) ,'DD/MM/YY hh:mm:ss') as DateTime


Br,

KC

Best Regards,
KC
rkpatelqlikview
Contributor III

Re: How to compare the rows with in One Column and Substract the time stamp

Dear Jyothish, Its working but i used  this in the  interval function. Int column not showing any thing.

Can you please check this expression?

   Interval( timestamp#(Ac.GI date, &' '&Time(PGI_Time) ,'DD/MM/YY hh:mm:ss')-Previous(timestamp#(Ac.GI date&' '& Time(PGI_Time) ,'DD/MM/YY hh:mm:ss')),'hh:mm:ss') as Int,

jyothish8807
Honored Contributor II

Re: How to compare the rows with in One Column and Substract the time stamp

Can you share the exact format of both date and time field ?

Best Regards,
KC
rkpatelqlikview
Contributor III

Re: How to compare the rows with in One Column and Substract the time stamp

Thanks for your great response.

Please consider A,B,C,D columns.

Store IDSalesAc.GI datePGI_Time

//Load *,

//if( [Vehicle Number]=Previous([Vehicle Number]) and peek(Int)<='2','',Sales) as NEw;

LOAD

timestamp#(Ac.GI date &' '& time(PGI_Time), 'DD/MM/YY hh:mm:ss') as DateTime,

   [Vehicle Number],

Time(PGI_Time) as Time,

     Sales,

     Ac.GI date,

   Interval( timestamp#(Ac.GI date &' '&  time(PGI_Time) ,'DD/MM/YY hh:mm:ss')-Previous(timestamp#(Ac.GI date&' '& time(PGI_Time),'DD/MM/YY hh:mm:ss')),'mm') as Int,

//     if( [Vehicle Number]=Previous( [Vehicle Number]) and Interval(Time#(Ac.GI date,'M.D.YYYY h:mm:ss')-Previous(Time#(Ac.GI date,'M.D.YYYY h:mm:ss')),'h')<='2',Quantity,'') as New1,

    

FROM

[D:\....

(qvd);



jyothish8807
Honored Contributor II

Re: How to compare the rows with in One Column and Substract the time stamp

Check this:

Load *,

interval(timestamp(timestamp#(Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY')&' '&time(Time#(Time,'hh:mm:ss')),'DD/MM/YY hh:mm:ss TT'),'DD/MM/YY hh:mm:ss TT') -

previous(timestamp(timestamp#(Date(Date#(Date,'DD/MM/YY'),'DD/MM/YY')&' '&time(Time#(Time,'hh:mm:ss')),'DD/MM/YY hh:mm:ss TT'),'DD/MM/YY hh:mm:ss TT')),'h') as Int ;

LOAD * INLINE [

    Date, Time

    25/08/17, 3:03:21

    25/08/17, 4:05:21

];

Br,

KC

Best Regards,
KC
rkpatelqlikview
Contributor III

Re: How to compare the rows with in One Column and Substract the time stamp

Sorry Jyothish, Not working this one