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

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

shashipraband — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Anil_Babu_Samineni — Tue, 30 Oct 2018 02:29:53 GMT

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

];

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

vitaliichupryna — Tue, 30 Oct 2018 13:12:15 GMT

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

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

Colin-Albert — Tue, 30 Oct 2018 13:37:04 GMT

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;

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

shashipraband — Tue, 30 Oct 2018 17:13:47 GMT

Hi Vitalii,

That is perfect, thank you. This worked great!

Regards

Shashi

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

shashipraband — Tue, 30 Oct 2018 17:37:41 GMT

Thanks Colin,

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

Regards

Shashi