Group on 3 fields?

[#1]

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	mail	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	mail	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?

[Gysbert_Wassenaar]

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;

[jagan]

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.

[#4]

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	mail	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	mail	X1	X2
13	077113	13@13.13
12	077112	12@12.12
16	077116	16@16.16

Hope this was clearer. Thanks

[preminqlik]

Hi

hope this helps you

Table2 :

Load

Code,

Phone

resident table where len(Code)>0;

left join

Load distinct 

Phone ,

Mail

resident Table;

drop table Table

regards

Premhas.

[#6]

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

[qlikpahadi07]

Marten I think you have a solution with the attachment I already shared