Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How do i group rows when i cant group on one field? the reason is shown underneath, sometimes thers no value in some of the field but the row has a value for exampel phone thats connect it to a nother row there i can find ex. code.
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 13@13.13 | |||
13 | 077113 | |||
077113 | 13@13.13 | |||
13@13.13 | ||||
12 | 077112 | 12@12.12 | ||
12 | 12@12.12 | |||
12 | 077112 | |||
16 | 16@16.16 | |||
16 | 077116 | |||
16 | 077116 | 16@16.16 | ||
077116 | 16@16.16 |
The wanted result is:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | 13@13.13 | ||
12 | 077112 | 12@12.12 | ||
16 | 077116 | 16@16.16 | ||
Think its a no brainer but i cant get it right. Any ideas?
SET NULLINTERPRET=NULL;
X2:
LOAD if(len(trim(code))=0,peek(code),code) as code, text(phone) as phone,mail
Inline [
code, phone, mail,
'13',NULL,NULL
'13','077113',NULL
NULL,'077113','13@13.13'
];
X3:
NoConcatenate
LOAD code, MaxString(phone) as phone, MaxString(mail) as mail
Resident X2
Group by code;
drop table X2;
Hi,
Try this
Dev:
LOAD
If(code = 'NULL', Previous(code), code) AS code,
If(phone = 'NULL', Null(), phone) AS phone,
If(mail = 'NULL', Null(), mail) AS mail
Inline [
code, phone, mail,
'13',NULL,NULL
'13','077113',NULL
NULL,'077113','13@13.13'
];
Dev2:
NoConcatenate
load
code
,MaxString(phone) as phone
,MaxString(mail) as mail
Resident Dev
Group By code;
DROP Table Dev;
Regards,
Jagan.
Okej, i post the problem again. And i trie to be a litle more specifik about the problem. I have updated the exampel tabel to make the problem more clear.
How do i group rows when i cant group on one field? the reason is shown underneath, sometimes thers no value in some of the field but the row has a value for exampel phone thats connect it to a nother row there i can find ex. code.
code | phone | X1 | X2 | |
---|---|---|---|---|
13@13.13 | ||||
13 | 077113 | |||
077113 | 13@13.13 | |||
12 | 12@12.12 | |||
12 | 077112 | |||
16 | 16@16.16 | |||
077116 | 16@16.16 |
13 - Connects on phone
12 - Connects on code
16 - Connects on mail
Inline:
Dev:
LOAD *
Inline [
code, phone, mail,
'','','13@13.13'
'13','077113',''
'','077113','13@13.13'
'12','','12@12.12'
'12','077112',''
'16','','16@16.16'
'','077116','16@16.16'
];
The wanted result is:
code | phone | X1 | X2 | |
---|---|---|---|---|
13 | 077113 | 13@13.13 | ||
12 | 077112 | 12@12.12 | ||
16 | 077116 | 16@16.16 | ||
Hope this was clearer. Thanks
Hi
hope this helps you
Table2 :
Load
Code,
Phone
resident table where len(Code)>0;
left join
Load distinct
Phone ,
resident Table;
drop table Table
regards
Premhas.
Hi,
Hope this helps you
//---------------------------------------------------------------------------------------------------
Dev:
LOAD * Inline [
code, phone, mail,
'','','13@13.13'
'13','077113',''
'','077113','13@13.13'
'12','','12@12.12'
'12','077112',''
'16','','16@16.16'
'','077116','16@16.16'
];
//---------------------------------------------------------------------------------------------------
Table1:
Load *, code_Table1&'|'&phone_Table1&'|'&mail_Table1 as Key;
Load if(code<>'',code,'XX') as code_Table1,
if(phone<>'',phone,'XXXXXX') as phone_Table1,
if(mail<>'',mail,'XXXXXXXX') as mail_Table1
Resident Dev; DROP Table Dev;
//---------------------------------------------------------------------------------------------------
MapCodePhone1:
Mapping LOAD Key, phone_Table1 Resident Table1;
//---------------------------------------------------------------------------------------------------
MapCodePhone2:
Mapping lOAD Right(Key,8) as Right9Key, phone_Table1 Resident Table1 Where phone_Table1 <> 'XXXXXX';
//---------------------------------------------------------------------------------------------------
MapCodemail1:
Mapping LOAD Left(Key,2) as left2Key, mail_Table1 Resident Table1;
//---------------------------------------------------------------------------------------------------
MapCodemail2:
Mapping Load Mid(Key,4,6) as Mid6Key, mail_Table1 Resident Table1 Where mail_Table1 <> 'XXXXXXXX';
//---------------------------------------------------------------------------------------------------
Tabel2:
Load Distinct code_Table1 as code,
if(ApplyMap('MapCodePhone1',Key,'XXXXXX') <> 'XXXXXX',
ApplyMap('MapCodePhone1',Key,'XXXXXX') ,
ApplyMap('MapCodePhone2',Right(Key,8),'XXXXXX')) as phone,
if(ApplyMap('MapCodemail1',Left(Key,2),'XXXXXXXX') <> 'XXXXXXXX',
ApplyMap('MapCodemail1',Left(Key,2),'XXXXXXXX'),
ApplyMap('MapCodemail2',Mid(Key,4,6),'XXXXXXXX')) as mail
Resident Table1
Where code_Table1 <> 'XX'
and if(ApplyMap('MapCodePhone1',Key,'XXXXXX') <> 'XXXXXX',
ApplyMap('MapCodePhone1',Key,'XXXXXX') ,
ApplyMap('MapCodePhone2',Right(Key,8),'XXXXXX')) <> 'XXXXXX';
DROP Table Table1;
//---------------------------------------------------------------------------------------------------
Regards,
NR
Marten I think you have a solution with the attachment I already shared