Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
For the Client AshokLeyland we are getting 2 date among that We need count as 2 and Required Minimum date
For the Client Tata Motors we are getting 3 date among that We need count as 3 and Required Minimum date
Client_Name | Date | |||
---|---|---|---|---|
|
| |||
|
| |||
Output Required is :
Client_Name | Date | Count |
---|---|---|
Ashok Leyland | 2017-12-08 14:42:35 | 2 |
Tata Motors | 2017-12-14 14:42:04 | 3 |
Is there any method/function to transformer the data as desired,Need some assistance
Try this
Table:
LOAD Client_Name,
TimeStamp(Max(Date)) as Date,
Count(Date) as Count
Group By Client_Name;
LOAD Client_Name,
SubField(NewDate, '@') as Date;
LOAD Client_Name,
Date,
If(Len(Trim(Date)) < 20, Date,
If(Len(Trim(Date)) < 40, Trim(Left(Trim(Date), 19)) & '@' & Trim(Right(Trim(Date), 19)),
If(Len(Trim(Date)) < 60, Trim(Left(Trim(Date), 19)) & '@' & Trim(Mid(Trim(Date), 21, 19)) & '@' & Trim(Right(Trim(Date), 19))))) as NewDate;
LOAD * INLINE [
Client_Name, Date
Ashok Leyland, 2017-12-08 14:42:04 2017-12-08 14:42:35
Tata Motors, 2017-12-14 14:42:04 2017-12-11 14:42:35 2017-12-08 14:42:04
];
So Date column comes with 2 or 3 dates in one cell?
Are you mean With max date? Because in Your Date required you are showing max date.
Some times 2,3 dates and some time single date only
Max is ok ,we need some assistance
Is there a separator between multiple dates? like a comma or pipe?
Space
Time being Im handling this in Slqserver using and its working
(LENGTH(GROUP_CONCAT(DISTINCT h_cp.`date` SEPARATOR ' ')) - LENGTH(REPLACE(GROUP_CONCAT(DISTINCT h_cp.`date` SEPARATOR ' '), ':', '')))/LENGTH(':') as cnt
Experts need some inputs in Qlikview
Try this
Table:
LOAD Client_Name,
TimeStamp(Max(Date)) as Date,
Count(Date) as Count
Group By Client_Name;
LOAD Client_Name,
SubField(NewDate, '@') as Date;
LOAD Client_Name,
Date,
If(Len(Trim(Date)) < 20, Date,
If(Len(Trim(Date)) < 40, Trim(Left(Trim(Date), 19)) & '@' & Trim(Right(Trim(Date), 19)),
If(Len(Trim(Date)) < 60, Trim(Left(Trim(Date), 19)) & '@' & Trim(Mid(Trim(Date), 21, 19)) & '@' & Trim(Right(Trim(Date), 19))))) as NewDate;
LOAD * INLINE [
Client_Name, Date
Ashok Leyland, 2017-12-08 14:42:04 2017-12-08 14:42:35
Tata Motors, 2017-12-14 14:42:04 2017-12-11 14:42:35 2017-12-08 14:42:04
];
This code is very impressive code :
If(Len(Trim(Date)) < 20, Date,
If(Len(Trim(Date)) < 40, Trim(Left(Trim(Date), 19)) & '@' & Trim(Right(Trim(Date), 19)),
If(Len(Trim(Date)) < 60, Trim(Left(Trim(Date), 19)) & '@' & Trim(Mid(Trim(Date), 21, 19)) & '@' & Trim(Right(Trim(Date), 19))))) as NewDate;
Tq