Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to label records in a transnational table

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:

IDtermination_dateFromTo
261988-12-201987-03-019999-12-31
26-1971-12-231976-05-31
26-1976-06-011987-02-28
600122011-07-072009-06-302011-05-24
600122011-07-072011-05-252011-07-07
60012-2007-02-042007-02-10
60012-2007-02-112007-02-24
60012-2007-02-252007-03-10
60012-2007-03-112007-04-21
60012-2007-04-222009-06-29
60012-2011-07-089999-12-31
4011418-2017-02-132017-02-17
4011418-2017-02-182017-06-06
4011418-2017-06-072017-06-22
4011418-2017-06-232017-06-23
4011418-2017-06-242017-06-30
4011418-2017-07-019999-12-31

table 2:

  

IDStatus
26Detached
60012Detached
4011418Attached

table 3:

    

IDtermination_dateFromToStatus
261988-12-201987-03-019999-12-31Detached
26-1971-12-231976-05-31Detached
26-1976-06-011987-02-28Detached
600122011-07-072009-06-302011-05-24Detached
600122011-07-072011-05-252011-07-07Detached
60012-2007-02-042007-02-10Detached
60012-2007-02-112007-02-24Detached
60012-2007-02-252007-03-10Detached
60012-2007-03-112007-04-21Detached
60012-2007-04-222009-06-29Detached
60012-2011-07-089999-12-31Detached
4011418-2017-02-132017-02-17Attached
4011418-2017-02-182017-06-06Attached
4011418-2017-06-072017-06-22Attached
4011418-2017-06-232017-06-23Attached
4011418-2017-06-242017-06-30Attached
4011418-2017-07-019999-12-31Attached
1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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:

percent.png

View solution in original post

5 Replies
cristinapo
Contributor III
Contributor III

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
Specialist
Specialist

PFA

kaanerisen
Creator III
Creator III

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:

percent.png

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;

OP10.PNG

Anonymous
Not applicable
Author

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.