Max date in comma separated date column

[prat1507]

Hi all

I've a column with comma separated date values. Approx 8000 records. I need to store the max date and min date in separate fields.

Kindly help.

Regards
Pratyush

[Anil_Babu_Samineni]

First you need to Bifurcate using SubField() ..

[sunny_talwar · Accepted solution]

Something along these lines

Table:

LOAD Max(Dates) as MaxDate,

Min(Dates) as MinDate;

LOAD SubField(Dates, ',') as Dates;

LOAD * INLINE [

    Dates

    200,300,400,500

] (delimiter IS '|');

[prat1507]

But how do I link this to my existing table?? Can I use resident load?

[sunny_talwar]

You sure can

[prat1507]

Hi My data is as below:

Invoice	Invoice Start Date	Invoice End Date
1	2017/06/01, 2017/06/01, 2017/06/01	2017/06/30, 2017/06/30, 2017/06/30
2	2017/07/01, 2017/07/01, 2017/07/01	2017/07/31, 2017/07/31, 2017/07/31
3	2017/08/01, 2017/08/01, 2017/08/01	2017/08/31, 2017/08/31, 2017/08/31
4	2017/10/01, 2017/10/01, 2017/10/01	2017/10/31, 2017/10/31, 2017/10/31
5	2017/06/01	2017/06/30
6	2017/06/01	2017/06/30
7	2017/07/01	2017/07/31
8	2017/08/01	2017/08/31
9	2017/09/01	2017/09/30
10	2017/10/01	2017/10/31
11	2017/05/01	2017/05/31
12	2017/06/01	2017/06/30
13	2017/07/01	2017/07/31
14	2017/04/17	2017/04/30
15	2017/04/01, 2017/04/01, 2017/05/01	2017/04/30, 2017/04/30, 2017/05/31

I need min of Invoice Start Date and max of Invoice End date, along side Invoice in a separate table from this data. Could you plz help me with this.

[prat1507]

Done on my own. Thanks a lot Sunny for the guidance.