Converting a single date field into 2 fields as StartDate and EndDate

[shashipraband]

Hi,

I am new to QlikView.I'm trying to split a date field into 2 fields but I cannot do it. I searched on the community but could not find the solution.

I have the below table as input. Date format is DD-MM-YYYY.

Temp:

Load * Inline[

EmpId, Location, Date

1,Hyderabad,01-01-2017

2, Mumbai, 01-01-2017

1, Hyderabad, 01-12-2017

];

Now I need to divide the Date field into StartDate and EndDate based on EmpId field.

The required output should be like below.

EmpId	Location	StartDate	EndDate
1	Hyderabad	01-01-2017	01-12-2017
2	Mumbai	01-01-2017	-

Please help me to solve this scenario.

Regards

Shashi

[Anil_Babu_Samineni]

What if data like this?

Temp:

Load * Inline[

EmpId, Location, Date

1,Hyderabad,01-01-2017

2,Hyderabad,07-01-2017

2, Mumbai, 01-01-2017

2, Mumbai, 07-01-2017

2, Mumbai, 10-01-2017

1, Hyderabad, 01-12-2017

];

[vitaliichupryna · Accepted solution]

Hi Slashi,

See possible solution below:

Temp:
Load * Inline[
     EmpId, Location, Date
     1,Hyderabad,01-01-2017
     2, Mumbai, 01-01-2017
     1, Hyderabad, 01-12-2017
];

StartEndDate:
Left Join (Temp)
Load
      EmpId,
      StartDate,
      If(StartDate <> EndDate, EndDate) AS EndDate;
Load
      EmpId,
      Date(Min(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS StartDate,
      Date(Max(Date#(Date, 'MM-DD-YYYY')),'MM-DD-YYYY') AS EndDate
Resident Temp
Group BY EmpId;

Thanks,

Vitalii

[Colin-Albert]

You can do this in the front-end of the application by adding a chart with EmpId & Location as dimensions and two expressions StartDate = min(Date)  and EndDate = max(Date).

Or in the load script

Load

     EmpId, Location, date#(Date, 'DD-MM-YYYY') as Date

Inline[

EmpId, Location, Date

1,Hyderabad,01-01-2017

2,Hyderabad,07-01-2017

2, Mumbai, 01-01-2017

2, Mumbai, 07-01-2017

2, Mumbai, 10-01-2017

1, Hyderabad, 01-12-2017

];

Data:

Load

     EmpId,

     Location,

     date(min(Date)) as StartDate,

     date(max(Date)) as EndDate

resident Temp

group by EmpId,     Location

;

drop table Temp;

[shashipraband]

Hi Vitalii,

That is perfect, thank you.  This worked great!

Regards

Shashi

[shashipraband]

Thanks Colin,

As per your instructions, I could do this in front-end.

Regards

Shashi