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

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

ravikumar_iyana — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

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

STOREID	Date Time	Sales	Result
A/F3630	02.05.2018 05:07:24	525	525
A/F3635	05.05.2018 09:10:29	101	101
A/F3635	05.05.2018 15:16:24	123	123
A/F3635	05.05.2018 16:52:29	123
A/F3638	11.05.2018 19:24:28	565	565
A/F3639	12.05.2018 18:26:25	141	141

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.

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

rkpatelqlikview — Mon, 14 May 2018 05:03:42 GMT

Hello Ravi,

Can you please provide sample data? It will be appreciated

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

jyothish8807 — Mon, 14 May 2018 05:52:09 GMT

Hi Ravi,

Pfa, Hope this is what you are looking for.

Br,

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

rkpatelqlikview — Mon, 14 May 2018 07:40:36 GMT

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))
));

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

jyothish8807 — Mon, 14 May 2018 08:18:00 GMT

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,

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

rkpatelqlikview — Mon, 14 May 2018 08:33:32 GMT

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,

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

jyothish8807 — Mon, 14 May 2018 08:43:02 GMT

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

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

rkpatelqlikview — Mon, 14 May 2018 08:53:17 GMT

Thanks for your great response.

Please consider A,B,C,D columns.

Store ID

Sales

Ac.GI date

PGI_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);

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

jyothish8807 — Mon, 14 May 2018 09:04:15 GMT

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,

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

rkpatelqlikview — Mon, 14 May 2018 09:21:38 GMT

Sorry Jyothish, Not working this one

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

jyothish8807 — Mon, 14 May 2018 09:53:24 GMT

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,

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

rkpatelqlikview — Mon, 14 May 2018 10:00:13 GMT

This one will be correct answer. I tried with my Actual Data.

There is Vehiclenumber transactions happened in the same data and time difference is also more than 2 hours, At this criteria NEw Column should get both values. 35 and 35.

If the Time difference is lessthan 2 hours at that time we can consider as only one value.

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

rkpatelqlikview — Mon, 14 May 2018 10:17:02 GMT

Great Jyothish, Its working fine. But the problem is here, Even we put the condition peek(Int)<='2', Its not considering the time difference. Below highlighted screenshot the time difference is more than 2 hours. Still the finalQuantity is not taken.

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

rkpatelqlikview — Mon, 14 May 2018 10:19:05 GMT

Got it Jyothish, I very sorry.

Instead Lessthan i put it Greater than.

@ Ravi, Hope you got the answer.

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

ravikumar_iyana — Mon, 14 May 2018 13:22:56 GMT

Thanks a Lot Jyothish