grouping in qlikview

[#1]

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

[Gysbert_Wassenaar]

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;

[#3]

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

[rbecher]

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

[marcus_malinow]

How about:

UserGroup:

LOAD DISTINCT lat1, RowNo() as Group

FROM .....

ORDER BY lat1;

LEFT JOIN (UserGroup)

LOAD lat1, MSISDN

FROM .....;

Marcus

[sushil353]

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;

[#7]

this is not working.

It is giving 8 distinct rows

[#8]

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

[marcus_malinow]

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 .....;

[Gysbert_Wassenaar]

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;