topic grouping in qlikview in QlikView

grouping in qlikview

Wed, 23 Oct 2013 10:58:28 GMT

lat1	MSISDN
1.3098	525-00-10001
1.3098	525-00-10003
1.3224	525-00-10002
1.3224	525-00-10004
1.3526	525-00-10002
1.3526	525-00-10004
1.3669	525-00-10001
1.3669	525-00-10003

hi all,

I had a table like specified above. i want to get the output like this

user group no

525-00-10001 1

525-00-10003 1

525-00-10002 2

525-00-10004 2

i want to get this from script

please let me know how to do this

Re: grouping in qlikview

Gysbert_Wassenaar — Wed, 23 Oct 2013 11:10:49 GMT

Why is 525-00-10001 in group 1 and not for example group 318?

load

lat1,

MSISDN

if(match(MSISDN,'525-00-10001','525-00-10003'),1,2) as [group no]

from mysource;

Re: grouping in qlikview

Wed, 23 Oct 2013 11:16:35 GMT

i just posted a sample here.

Basic thing is i want to find users who are moving together.

as here 10001 and 10003 are moving together. they have different lat. But both are present.

So i want to club them in one group

similarly 10002 and 10004.

I cannot hard code the values

Re: grouping in qlikview

rbecher — Wed, 23 Oct 2013 11:26:46 GMT

In your example the lat is the same for 10001, 10003 and so on.. What would be a realistic difference? Maybe you can group on a rounded lat?

- Ralf

Re: grouping in qlikview

marcus_malinow — Wed, 23 Oct 2013 11:29:08 GMT

How about:

UserGroup:

LOAD DISTINCT lat1, RowNo() as Group

FROM .....

ORDER BY lat1;

LEFT JOIN (UserGroup)

LOAD lat1, MSISDN

FROM .....;

Marcus

Re: grouping in qlikview

sushil353 — Wed, 23 Oct 2013 11:33:15 GMT

Try this:

tab:

LOAD * Inline

[

lat1, MSISDN

1.3098, 525-00-10001

1.3098,525-00-10003

1.3224,525-00-10002

1.3224,525-00-10004

1.3526,525-00-10002

1.3526,525-00-10004

1.3669,525-00-10001

1.3669,525-00-10003

];

temp:

LOAD Distinct lat1

Resident tab;

Left Join(tab)

temp2:

LOAD lat1,RowNo() as group

Resident temp;

DROP Table temp;

Re: grouping in qlikview

Wed, 23 Oct 2013 11:45:22 GMT

this is not working.

It is giving 8 distinct rows

Re: grouping in qlikview

Wed, 23 Oct 2013 11:48:01 GMT

it may differ ,

some point of time

1001 can get some other lat and similarly others. i dont want to conside those.

I only want to consider when they have same lat

Re: grouping in qlikview

marcus_malinow — Wed, 23 Oct 2013 11:50:28 GMT

Actually I think Sushil's example may be closer:

UserGrouptemp:

LOAD DISTINCT lat1

FROM .....

ORDER BY lat1;

UserGroup

LOAD lat1, RowNo() as Group

RESIDENT UserGrouptemp;

DROP TABLE UserGrouptemp;

LEFT JOIN (UserGroup)

LOAD lat1, MSISDN

FROM .....;

Re: grouping in qlikview

Gysbert_Wassenaar — Tue, 24 Dec 2013 07:10:20 GMT

Maybe like this:

Input:

LOAD * INLINE [

lat1, MSISDN

1.3098, 525-00-10001

1.3098, 525-00-10003

1.3224, 525-00-10002

1.3224, 525-00-10004

1.3526, 525-00-10002

1.3526, 525-00-10004

1.3669, 525-00-10001

1.3669, 525-00-10003

];

Groups:

load MSISDN, AutoNumber(concat(lat1,'|'),'group') as Group

Resident Input

group by MSISDN;

Re: grouping in qlikview

Tue, 24 Dec 2013 07:22:26 GMT

Try this

Load Autonumber(Lat1) as Group, MSISDN as user resident tablename;

Regards,

Arun