cell with multiple date count and Min Date Required

[Anonymous]

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

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

[sunny_talwar]

So Date column comes with 2 or 3 dates in one cell?

[stabben23]

Are you mean With max date? Because in Your Date required you are showing max date.

[Anonymous]

Some times 2,3 dates and some time single date only

[Anonymous]

Max is ok ,we need some assistance

[sunny_talwar]

Is there a separator between multiple dates? like a comma or pipe?

[Anonymous]

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

[sunny_talwar · Accepted solution]

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

];

[Anonymous]

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