Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Need to display only Max Insert_Timestamp records if it has same date.
On 18 it has 2 records, i need to display only Max(Insert_Timestamp) and remaining records.
Input | |||
ID | LoadErrors | Insert_Timestamp | Environment |
321 | 5 | 2022-04-25 14:04:37 | System |
634 | 10 | 2022-04-25 14:04:37 | Clock |
221 | 6 | 2022-04-18 12:10:50 | System |
432 | 2 | 2022-04-18 12:10:50 | Clock |
345 | 1 | 2022-04-18 12:08:15 | System |
234 | 8 | 2022-04-18 12:08:15 | Clock |
342 | 12 | 2022-03-10 07:10:25 | System |
235 | 4 | 2022-03-10 07:10:25 | Clock |
Output | |||
ID | LoadErrors | Insert_Timestamp | Environment |
321 | 5 | 2022-04-25 14:04:37 | System |
634 | 10 | 2022-04-25 14:04:37 | Clock |
221 | 6 | 2022-04-18 12:10:50 | System |
432 | 2 | 2022-04-18 12:10:50 | Clock |
342 | 12 | 2022-03-10 07:10:25 | System |
235 | 4 | 2022-03-10 07:10:25 | Clock |
You could have a look at this script function and see if it's helpful:
18? which 18?
well..
It would be something like this :
YourTable:
load
ID,
LoadErrors,
Insert_Timestamp,
Environment
from source;
right join
load ID,
timestamp(max(Insert_Timestamp),'YYYY-MM-DD hh:mm:ss') as Insert_Timestamp
resident YourTable group by ID;
Hi,
Thanks for your quick response.
Code in the Load Editor is :
[COUNT_TABLE]:
SELECT "ID",
"LOADERRORS",
"Insert_Timestamp",
"Environment "
FROM "Table"."COUNT_TABLE";
LOAD ID,
LOADERRORS,
Insert_Timestamp,
Environment ;
As i am new to the Qliksense , can you please help me how to use Join for the above code.
Hi Team,
Can any one help me on this.
Either in chart function expression or in data load editor.
Thanks
As below
tmp:
Load *,Date(floor(timestamp#(Insert_Timestamp,'YYYY-MM-DD hh:mm:ss'))) as insert_Date inline [
ID,LoadErrors,Insert_Timestamp,Environment
321,5,2022-04-25 14:04:37,System
634,10,2022-04-25 14:04:37,Clock
221,6,2022-04-18 12:10:50,System
432,2,2022-04-18 12:10:50,Clock
345,1,2022-04-18 12:08:15,System
234,8,2022-04-18 12:08:15,Clock
342,12,2022-03-10 07:10:25,System
235,4,2022-03-10 07:10:25,Clock
];
Right Join(tmp)
Load
insert_Date,Environment,timestamp(max(Insert_Timestamp),'YYYY-MM-DD hh:mm:ss') as Insert_Timestamp
Resident tmp
Group by insert_Date,Environment
;
exit Script;