topic Re: How to identify the chain? in QlikView

How to identify the chain?

Mon, 19 May 2014 14:27:45 GMT

Hello

I have source with structure: SOMEONE1, SOMEONE2, family_identifier.

SOMEONE in one row can be associated with someone from a different row. Thus, in the family for 3 person. And so on ...

Help me identify the chain effective way?>

Re: How to identify the chain?

Mon, 19 May 2014 14:31:55 GMT

May you show sample data?

Re: How to identify the chain?

hic — Mon, 19 May 2014 15:04:01 GMT

This sounds like a hierarchy. If SOMEONE1 has a parent SOMEONE2, then you can resolve this hierarchy using a Hierarchy Load.

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies

HIC

Re: How to identify the chain?

Mon, 19 May 2014 16:49:55 GMT

in fact I work with numbers:

Re: How to identify the chain?

Tue, 20 May 2014 08:56:40 GMT

Gertye,

i thought that HierarchyBelongsTo() may be a key, but i understand, that this relation is not hierarchical in fact. I undestand, that someone1 konws someone2 and taht means, that someone2 knows someone1 ....

hmm.

1. How many rows do you have?

2 Are you interested to find "shortest way" (shortest ways) from SMB_X to SMB_Y or maybe all possible ways?

3.How many steps do you expect (i heard somewhere, that from any to any other person in the world you need no more than 7 steps ....)

regards

Darek

Re: How to identify the chain?

Tue, 20 May 2014 09:01:32 GMT

I think we may need something like Dijkstra's algorithm...

reg

Re: How to identify the chain?

Tue, 20 May 2014 09:26:33 GMT

Hello Dariusz

1. How many rows do you have?

It's 1 272 055 rows for the start (at the end it can be about 20 000 000 rows).

2 Are you interested to find "shortest way" (shortest ways) from SMB_X to SMB_Y or maybe all possible ways?

I'd like shortest way (I know how I can make it, but I don't like my way).

3.How many steps do you expect (i heard somewhere, that from any to any other person in the world you need no more than 7 steps ....)

Yes, I don't know where the bottom ;( Maximum number of people in the family ...mmm.. may be.. 20?

Re: How to identify the chain?

Tue, 20 May 2014 09:27:25 GMT

thanks, I will try it and let you know then

Re: How to identify the chain?

Tue, 20 May 2014 09:29:24 GMT

SOURCE		COMBINATIONS		RESULTS

1	2	1	2	1
2	3	1	3	2
2	4	1	4	2
2	5	1	5	2
4	7	1	7	3
		2	3	1
		2	4	1
		2	5	1
		2	7	2
		3	4	2
		3	5	2
		3	7	3
		4	5	2
		4	7	1
		5	7	3

Is it something like this?

Re: How to identify the chain?

Tue, 20 May 2014 09:30:55 GMT

yes, it was "my way" ))

Re: How to identify the chain?

Tue, 20 May 2014 09:35:50 GMT

Gertye,

tell me what is your goal. What you need in application. I can imagine that user selects 2 persons and application shows shortest distance (or maybe this distance and path).

Is it anything else you would like to do from those data in application?

regards

Darek

Re: How to identify the chain?

Tue, 20 May 2014 09:52:21 GMT

this table it's source table for my qlikview model

I need to define relations between people and generate family_ID

After that users of my qlikview model will be able to see family members. They will be able to learn any person Family_ID and then(select Family_ID) to know who is entering person family. That's all.

Re: How to identify the chain?

Tue, 20 May 2014 09:57:20 GMT

Gertye,

so, i understand that if it is any path from one person to some other person (it is not importank how long it is) - those persons will get the same family id? You, in fact, are not interested what is the distance....

regards

Darek

Re: How to identify the chain?

Tue, 20 May 2014 09:59:04 GMT

You, in fact, are not interested what is the distance....

That's right

Re: How to identify the chain?

preminqlik — Tue, 20 May 2014 10:15:35 GMT

hi ,

please find attachment

Re: How to identify the chain?

Tue, 20 May 2014 11:12:02 GMT

Let me know if it is true, that:

We can assume, that reload is incremental. We know some families defined earlier (during earlier reloads) and you need to incorporate new contacts (pairs of persons). Each pair can:

-build new family (if we dont know any person from this pair),

- connect to one existing family (if we found 1 existing family for this pair)

- cause, that 2 families become one family (we know both persons but they were in another families).

Is it possible that some earlier defined pairs will be dropped in the future?

Regards
Darek

Re: How to identify the chain?

Tue, 20 May 2014 11:34:49 GMT

Let me know if it is true, that:

We can assume, that reload is incremental. We know some families defined earlier (during earlier reloads) and you need to incorporate new contacts (pairs of persons). Each pair can:

-build new family (if we dont know any person from this pair),

yes

- connect to one existing family (if we found 1 existing family for this pair)

- cause, that 2 families become one family (we know both persons but they were in another families).

yes

Is it possible that some earlier defined pairs will be dropped in the future?

I don't think so. My user needs to have contact with people in family. If person1 in marriage divorse with person2, my user still can call to person1 for to ask about person2. It's eternal connection

Re: Re: How to identify the chain?

Tue, 20 May 2014 13:34:37 GMT

Prem,

i prepared example for you.

Do few "incremental" reloads and look how families are rebuild after them.

1. save attached files in d:\

(or in other location, but then change pathes in script)

2. reload and look

3. put new data into sheet1 (Arkusz1) of xls

4.reload ....., reload ....

Let me know what do you think about it.

regards

Darek !

Re: Re: How to identify the chain?

Tue, 20 May 2014 13:46:12 GMT

Thanks for help!

Re: Re: How to identify the chain?

Tue, 20 May 2014 13:51:02 GMT

That's it!

Darek, thank you.

You offered a good solution, I want use it, if you do not mind...