topic Re: Re: Group on 3 fields? in QlikView

Group on 3 fields?

Tue, 17 Dec 2013 19:13:53 GMT

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

Re: Group on 3 fields?

Gysbert_Wassenaar — Tue, 17 Dec 2013 19:23:29 GMT

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.

Re: Group on 3 fields?

Tue, 17 Dec 2013 19:30:14 GMT

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

Re: Group on 3 fields?

Gysbert_Wassenaar — Tue, 17 Dec 2013 19:32:30 GMT

Something like:

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

from ...somewhere...

group by code;

Re: Group on 3 fields?

Tue, 17 Dec 2013 19:55:19 GMT

dose that realy work on:

code	phone	mail
13
13	077113
	077113	13@13.13

Re: Group on 3 fields?

Gysbert_Wassenaar — Tue, 17 Dec 2013 20:04:57 GMT

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.

Re: Group on 3 fields?

Tue, 17 Dec 2013 20:16:45 GMT

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

Re: Group on 3 fields?

jagan — Wed, 18 Dec 2013 02:32:06 GMT

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.

Re: Re: Group on 3 fields?

qlikpahadi07 — Wed, 18 Dec 2013 05:05:57 GMT

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

Re: Group on 3 fields?

Wed, 18 Dec 2013 07:05:56 GMT

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?

Re: Re: Group on 3 fields?

Gysbert_Wassenaar — Wed, 18 Dec 2013 07:22:44 GMT

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;

Re: Group on 3 fields?

jagan — Wed, 18 Dec 2013 07:24:56 GMT

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.

Re: Group on 3 fields?

Wed, 18 Dec 2013 07:30:38 GMT

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

Hope this was clearer. Thanks

Re: Group on 3 fields?

preminqlik — Wed, 18 Dec 2013 08:46:34 GMT

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.

Re: Group on 3 fields?

Thu, 19 Dec 2013 03:19:11 GMT

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,

Re: Group on 3 fields?

qlikpahadi07 — Thu, 19 Dec 2013 10:21:03 GMT

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