Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hello Ravi,
Can you please provide sample data? It will be appreciated
Hi Ravi,
Pfa, Hope this is what you are looking for.
Br,
KC
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))
));
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
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,
Can you share the exact format of both date and time field ?
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);
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
Sorry Jyothish, Not working this one