topic cell with multiple date count and Min Date Required in QlikView

cell with multiple date count and Min Date Required

Anonymous — Thu, 14 Dec 2017 14:17:39 GMT

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

Re: cell with multiple date count and Min Date Required

sunny_talwar — Thu, 14 Dec 2017 14:20:48 GMT

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

Re: cell with multiple date count and Min Date Required

stabben23 — Thu, 14 Dec 2017 14:25:00 GMT

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

Re: cell with multiple date count and Min Date Required

Anonymous — Thu, 14 Dec 2017 14:26:09 GMT

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

Re: cell with multiple date count and Min Date Required

Anonymous — Thu, 14 Dec 2017 14:26:42 GMT

Max is ok ,we need some assistance

Re: cell with multiple date count and Min Date Required

sunny_talwar — Thu, 14 Dec 2017 14:36:58 GMT

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

Re: cell with multiple date count and Min Date Required

Anonymous — Thu, 14 Dec 2017 14:38:27 GMT

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

Re: cell with multiple date count and Min Date Required

sunny_talwar — Thu, 14 Dec 2017 14:54:33 GMT

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

];

Re: cell with multiple date count and Min Date Required

Anonymous — Thu, 14 Dec 2017 14:59:23 GMT

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;