How to label records in a transnational table

[Anonymous]

Hi everyone,

I need to create table 2 from table 1 below, or convert table 1 to table 3 (basically marking any IDs that have a termination date as "Detached" and any IDs that never have a termination date as "Attached"). I have been struggling with this for a few days. Can anyone help please?

Thanks very much in advance!

    table 1:

ID	termination_date	From	To
26	1988-12-20	1987-03-01	9999-12-31
26	-	1971-12-23	1976-05-31
26	-	1976-06-01	1987-02-28
60012	2011-07-07	2009-06-30	2011-05-24
60012	2011-07-07	2011-05-25	2011-07-07
60012	-	2007-02-04	2007-02-10
60012	-	2007-02-11	2007-02-24
60012	-	2007-02-25	2007-03-10
60012	-	2007-03-11	2007-04-21
60012	-	2007-04-22	2009-06-29
60012	-	2011-07-08	9999-12-31
4011418	-	2017-02-13	2017-02-17
4011418	-	2017-02-18	2017-06-06
4011418	-	2017-06-07	2017-06-22
4011418	-	2017-06-23	2017-06-23
4011418	-	2017-06-24	2017-06-30
4011418	-	2017-07-01	9999-12-31

table 2:

ID	Status
26	Detached
60012	Detached
4011418	Attached

table 3:

ID	termination_date	From	To	Status
26	1988-12-20	1987-03-01	9999-12-31	Detached
26	-	1971-12-23	1976-05-31	Detached
26	-	1976-06-01	1987-02-28	Detached
60012	2011-07-07	2009-06-30	2011-05-24	Detached
60012	2011-07-07	2011-05-25	2011-07-07	Detached
60012	-	2007-02-04	2007-02-10	Detached
60012	-	2007-02-11	2007-02-24	Detached
60012	-	2007-02-25	2007-03-10	Detached
60012	-	2007-03-11	2007-04-21	Detached
60012	-	2007-04-22	2009-06-29	Detached
60012	-	2011-07-08	9999-12-31	Detached
4011418	-	2017-02-13	2017-02-17	Attached
4011418	-	2017-02-18	2017-06-06	Attached
4011418	-	2017-06-07	2017-06-22	Attached
4011418	-	2017-06-23	2017-06-23	Attached
4011418	-	2017-06-24	2017-06-30	Attached
4011418	-	2017-07-01	9999-12-31	Attached

[cristinapo]

Hi,

When you load table 1, add a calculated field like this

If(IsNull([termination_date]), 'Attached','Detached')    AS [Status]

Hope it helps

Cristina

[aarkay29]

PFA

[kaanerisen · Accepted solution]

Hi,

Script:

Map:

mapping load ID,MaxString('Detached') as Status

group by ID;

load * Inline [

ID,termination_date,From,To

26,'1988-12-20','1987-03-01','9999-12-31'

26,,'1971-12-23','1976-05-31'

26,,'1976-06-01','1987-02-28'

60012,'2011-07-07','2009-06-30','2011-05-24'

60012,'2011-07-07','2011-05-25','2011-07-07'

60012,,'2007-02-04','2007-02-10'

60012,,'2007-02-11','2007-02-24'

60012,,'2007-02-25','2007-03-10'

60012,,'2007-03-11','2007-04-21'

60012,,'2007-04-22','2009-06-29'

60012,,'2011-07-08','9999-12-31'

4011418,,'2017-02-13','2017-02-17'

4011418,,'2017-02-18','2017-06-06'

4011418,,'2017-06-07','2017-06-22'

4011418,,'2017-06-23','2017-06-23'

4011418,,'2017-06-24','2017-06-30'

4011418,,'2017-07-01','9999-12-31'

]

where len(termination_date)>0;

Master:

load *,

ApplyMap('Map',ID,'Attached') as Status

Inline [

ID,termination_date,From,To

26,'1988-12-20','1987-03-01','9999-12-31'

26,,'1971-12-23','1976-05-31'

26,,'1976-06-01','1987-02-28'

60012,'2011-07-07','2009-06-30','2011-05-24'

60012,'2011-07-07','2011-05-25','2011-07-07'

60012,,'2007-02-04','2007-02-10'

60012,,'2007-02-11','2007-02-24'

60012,,'2007-02-25','2007-03-10'

60012,,'2007-03-11','2007-04-21'

60012,,'2007-04-22','2009-06-29'

60012,,'2011-07-08','9999-12-31'

4011418,,'2017-02-13','2017-02-17'

4011418,,'2017-02-18','2017-06-06'

4011418,,'2017-06-07','2017-06-22'

4011418,,'2017-06-23','2017-06-23'

4011418,,'2017-06-24','2017-06-30'

4011418,,'2017-07-01','9999-12-31'

];

Result:

[its_anandrjs]

May be this do this as attached script and found out the termination date and file with flag.

Table1:

LOAD * INLINE [

    ID, termination_date, From, To

    26, 1988-12-20, 1987-03-01, 9999-12-31

    26, , 1971-12-23, 1976-05-31

    26, , 1976-06-01, 1987-02-28

    60012, 2011-07-07, 2009-06-30, 2011-05-24

    60012, 2011-07-07, 2011-05-25, 2011-07-07

    60012, , 2007-02-04, 2007-02-10

    60012, , 2007-02-11, 2007-02-24

    60012, , 2007-02-25, 2007-03-10

    60012, , 2007-03-11, 2007-04-21

    60012, , 2007-04-22, 2009-06-29

    60012, , 2011-07-08, 9999-12-31

    4011418,, 2017-02-13, 2017-02-17

    4011418,, 2017-02-18, 2017-06-06

    4011418,, 2017-06-07, 2017-06-22

    4011418,, 2017-06-23, 2017-06-23

    4011418,, 2017-06-24, 2017-06-30

    4011418,, 2017-07-01, 9999-12-31

];

Left Join(Table1)

Table2:

LOAD *,if(Cnt > 1,'Detached','Attached') as StatusNew;

LOAD Distinct ID,

Count(DISTINCT termination_date) as Cnt

Resident Table1 Group By ID;

DROP Field Cnt;

[Anonymous]

Wow! Spot on kaan! Did you use MaxString for the sake of the "group by"? As I googled MaxString and it seems that it's usually used in an existing field.