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]

In a straight table you can use code as dimension and expressions like maxstring(phone) and maxstring(mail). And enable Suppress When Value is Null for the code dimension.

[#3]

Okej, but i want to do it in the load script. Is it possible?

[Gysbert_Wassenaar]

Something like:

Load code, maxstring(phone) as phone, maxstring(mail) as mail

from ...somewhere...

group by code;

[#5]

dose that realy work on:

code	phone	mail	X1	X2
13
13	077113
	077113	13@13.13

[Gysbert_Wassenaar]

Yes, it works perfectly. It does not make any guessed but works with the data given. Nulls stay nulls and are not replaced by random values or values that would result from an algorithm that is not specified.

If it does not do what you want, then you did not specify what should happen.

[#7]

My script:

SET NULLINTERPRET=NULL;

  Dev:

  LOAD *

  Inline [

  code, phone, mail,

      '13',NULL,NULL

      '13','077113',NULL

      NULL,'077113','13@13.13'

  ];

  Dev2:

  load

  code

  ,MaxString(phone) as phone

  ,MaxString(mail) as mail

  Resident Dev

  Group By code;

  DROP Table Dev;

Makes:

code	phone	mail	X1	X2
13	077113
	077113	13@13.13

My goal:

code	phone	mail	X1	X2
13	077113	13@13.13

[jagan]

Hi,

Try like this

Dev:

  LOAD *

  Inline [

  code, phone, mail,

      '13',NULL,NULL

      '13','077113',NULL

      NULL,'077113','13@13.13'

  ];

  Dev2:

  load

  code

  ,MaxString(phone) as phone

  ,MaxString(mail) as mail

  Resident Dev

WHERE Not IsNull(phone) or Not IsNull(mail)   // This removes Null records

  Group By code;

  DROP Table Dev;

Regards,

Jagan.

[qlikpahadi07]

HI Marten,

I have created the Sample App based on your data and applied same logic which gwassenaar had mentioned through UI/Script.

find the attachment

[#10]

Outputs

code	phone	mail	X1	X2
13	077113
	077113	13@13.13

My goal

code	phone	mail	X1	X2
13	077113	13@13.13

Group on 3 fields?