Relationships between key values ($Syn Tables and Circular References)

[jwaligora]

Hi,

SCENARIO

I have a table with animal details:

Animals:

LOAD Animal,

     ID,

     Age,

     Sex

FROM

[.\Animals.xlsx]

(ooxml, embedded labels, table is Sheet1);

The animals include Tigers and Bunnies. Due to a security breach, some of the tigers got into the Bunny enclosure and ate a few of the Bunnies.

I have a second table that outlines who ate who;

Ate:

LOAD Tiger_ID,

          Bunny_ID

FROM

[.\Animals_eat.xlsx]

(ooxml, embedded labels, table is Sheet1);

Both Tiger_ID and Bunny_ID belong to the set of IDs present in the Animal table.

QUESTION

I would like to create an association such that selecting an ID which belongs to a culprit tiger would display both the details about said tiger as well as the bunnies it consumed. Conversely, selecting an ID of a consumed bunny, would show information about the bunny as well as its murderer.

What is the recommended approach to apply in the data loading script?

Do I need to break up the Animal table, such that Tigers have one table and Bunnies have another? That would not be ideal for all my other charts/metrics, but trying to make it work via other means has thus far yielded no positive results.

Thanks,
J.

[Anil_Babu_Samineni]

How is your data model looks

I would request you to create two excels and then share and tell us with your expected out put?

Even we can follow this approach to prove. Or we can create single file

Animals:

LOAD RowNo() as RowNo,Animal,

     ID,

     Age,

     Sex

FROM

[.\Animals.xlsx]

(ooxml, embedded labels, table is Sheet1);

Ate:

LOAD RowNo() as RowNo,Tiger_ID,

          Bunny_ID

FROM

[.\Animals_eat.xlsx]

(ooxml, embedded labels, table is Sheet1);

This will prove associate for your model

Or

Animals:

Mapping LOAD Animal,

     ID,

     Age,

     Sex

FROM

[.\Animals.xlsx]

(ooxml, embedded labels, table is Sheet1);

Ate:

LOAD Tiger_ID,

          Bunny_ID

FROM

[.\Animals_eat.xlsx]

(ooxml, embedded labels, table is Sheet1);

Left Join(Ate)

Load Tiger_ID, Bunny_ID, ApplyMap('Animals',Animal) as Animal, ID, Age, Sex

Resident Animals;

Drop Table Ate;

[ahaahaaha]

Hi Joanna,

Maybe so.  When loading table Ate to create new field [KEY} with composite key, format such as  'Tiger_ID'&'Bunny_ID'. Then when loading table Animals to create a similar field [KEY}. Each records of table Animals check if the coincidence of the values of fields ID and  fields Tiger_ID or Bunny_ID in the table Ate, in field [Key] table Animals recorded the corresponding key 'Tiger_ID'&'Bunny_ID' format.

Thus both tables will be contacted as a result of a composite key.

This is an example script code

//create table Ate

Ate:

LOAD

RecNo() as Tiger_ID,

RecNo()*10 + 41 as Bunny_ID

AutoGenerate 5;

//create a composite key for table Ate

Left Join

LOAD*,

Tiger_ID&'|'&Bunny_ID as Key

Resident Ate;

//create temp Key Table

NoConcatenate

TableKey:

LOAD

If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

Key

Resident Ate

While IterNo()<=2;

//create table Animals

Animals:

LOAD If(RecNo()<=50, 'Tiger'&RecNo(), 'Bunny'&RecNo()) as Animal,

     RecNo() as ID,

     Div(Rand()*10, 1) as Age,

     If(Rand()>0.5, 'F', 'M') as Sex

AutoGenerate 100;

//create a composite key for table Animals

Left Join(Animals)

LOAD

ID_Key as ID,

Key

Resident TableKey;

DROP Table TableKey;

Result

The data generated in the script automatically, so you can just copy the code at QlikView or Qlik Sense

Regards,

Andrey

[jwaligora]

Hi loveisfail‌, ahaahaaha‌!

Thanks so much for your suggestions. Sorry for not posting the source data. I'm adding it now for reference. Part of the problem is that it could be a one-to-many mapping, and quite possibly even a many-to-many mapping (it was a bloodbath). I opted for a somewhat simplistic solution of dumping everything into a single table. It isn't exactly the level of automation I hoped for, but I can work around it (and chances are what I had in mind would by definition generate a circular reference). I have two toggle buttons 'I ate it.' and 'It ate me.' that filter out each type of data.

...Andrey, your implementation is an excellent, concise study of AutoGenerate, IterNo(), RecNo() and a few other QV concepts. I'll be starring at it for a while.

Animals:

LOAD Animal,

     ID,

     Age,

     Sex

FROM

[.\Animals.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join

LOAD Tiger_ID as ID,

     Bunny_ID

FROM

[.\Animals_eat.xlsx]

(ooxml, embedded labels, table is Sheet1);

left join

LOAD Bunny_ID as ID,

     Tiger_ID

FROM

[.\Animals_eat.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ahaahaaha]

Hi Joanna,

We take into account your remark about the a many-to-many mapping to create a composite key.

In this case, the script code for your data may be as follows.

//load Animals_eat

Directory;

TableAnimals_eat:

LOAD Tiger_ID,

    Bunny_ID

FROM

Animals_eat.xlsx

(ooxml, embedded labels, table is Sheet1);

//create composite key

Left Join

LOAD

Tiger_ID,

Tiger_ID&'|'&Concat(Bunny_ID, '|') as KeyEat

Resident TableAnimals_eat

Group By Tiger_ID;

//create temp Key Table

NoConcatenate

TableKey:

LOAD Distinct

If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

KeyEat

Resident TableAnimals_eat

While IterNo()<=2;

//load Animals

Animals:

Directory;

LOAD Animal,

    ID,

    Age,

    Sex

FROM

Animals.xlsx

(ooxml, embedded labels, table is Sheet1);

//create a composite key for table Animals

Left Join(Animals)

LOAD

ID_Key as ID,

KeyEat

Resident TableKey;

DROP Table TableKey;

Result

I hope this is what you need.

Regards,

Andrey

[ahaahaaha]

The above example shows in fact one-to-many mapping (one Tiger eats one or some Bunnys). If some Tigers eats some Bunnys, there is many-to-many mapping. In this case, it seems to me, must be new field in the table Animals_eat.xlsx, which contains a unique value for each case of eating (for example field "Case" in the attached file xlsx).

The value of this new field will be included in a composite key when linking tables.

Regards,

Andrey

[jwaligora]

Yes, my example was simplistic. When I alluded many-to-many, what I had in mind is two or more tigers sharing the same bunny, rather than a case classification of consumption instances. Like so:

Tiger_ID     Bunny_ID

T03            B01

T03            B02

T03            B04 B10

T05            B10

Thanks,
J.

[ahaahaaha]

Yes, I have already analyzed this situation. Source file

A working code for this event next

//load Animals_eat

Directory;

TableAnimals_eat:

LOAD Tiger_ID,

     Bunny_ID

FROM

Animals_eat.xlsx

(ooxml, embedded labels, table is Sheet1);

//create composite key whom ate

Left Join(TableAnimals_eat)

KeyEatWhom:

LOAD

Tiger_ID,

Concat(Bunny_ID, '|') as KeyEatWhom

Resident TableAnimals_eat

Group By Tiger_ID;

//create composite key who ate

Left Join(TableAnimals_eat)

KeyEatWho:

LOAD

Bunny_ID,

Concat(Tiger_ID, '|') as KeyEatWho

Resident TableAnimals_eat

Group By Bunny_ID;

//create composite common key

Left Join(TableAnimals_eat)

LOAD*,

KeyEatWho&'|'&KeyEatWhom as KeyEatCommon

Resident TableAnimals_eat;

DROP Fields KeyEatWho, KeyEatWhom From TableAnimals_eat;

//create temp Key Table

NoConcatenate

TableKey:

LOAD Distinct

If(IterNo()=1, Tiger_ID, Bunny_ID) as ID_Key,

KeyEatCommon

Resident TableAnimals_eat

While IterNo()<=2;

//load Animals

Animals:

Directory;

LOAD Animal,

     ID,

     Age,

     Sex

FROM

Animals.xlsx

(ooxml, embedded labels, table is Sheet1);

//create a composite key for table Animals

Left Join(Animals)

LOAD

ID_Key as ID,

KeyEatCommon

Resident TableKey;

DROP Table TableKey;

Result

However, if out of the source file to delete the last line, we get the result

Look carefully at the resulting synthetic keys. In the latter case we have "a porridge".

Brief conclusions:

1. With many-to-many mapping quality of the analysis depends on the quality of the source data. And since they will all be entered manually, mistakes are possible. Errors will be in the analysis.

2. If you ignore the many-to-many mapping eating each case is unique (I wrote above about it), you can get a data structure unsuitable for analysis. I think it will build the right relationship between the tables. But have yet think.

3. In my opinion, these considerations are valid for both discussed ways to build data, as for the left join tables in your proposal and for my proposal to use synthetic keys.

I hope that my considerations are correct, and I did not introduce you astray.

Regards,

Andrey

[jwaligora]

Hi Andrey,

I'm attaching a version with my 'big table' implementation. The first tab 'Main_view' demonstrates what associative behaviour I'm after. (Only the variable value picker and the two tables below it are of relevance; the ones on the side are just for reference.)

Your implementation sits in its own script tab named 'Andrey'. Promoting it to 1st script tab will run it instead of my 'Main' code. On the second page of the layout, labelled 'Andrey_view' is a duplicate of the 'Main_view' layout. I can't figure out how to define the expressions in the 'Variable Selection Output' table and the 'Reverse Lookup' table, to obtain the same result using  the 'Andrey' load as using the 'Main' load. Would it require a different set of keys?

In case I over-confused the matter (which I know I did), I'm ready to apologize with some muffins, or given your name, possibly a bottle of vodka.

Thanks,

J

[ahaahaaha · Accepted solution]

Hi Joanna,

For example, on the sheet 'In work" two diagrams with the new data structure. We continue to work on. Just do not have enough free time.

Regards,

Andrey