Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Making relations with different tables based on data from another table

Hi people!

so, here's the problem:

in the model I'm trying to build there is a relation which I think is kind of complicated. Movement, has a relation to Location via the idUbicacionConsumo OR the idUbicacionEntrega fields....

the condition which establishes with which field is the former relation created, is %KEY_product_type => if it is 1 or 3, the relation is to be created with idUbicacionConsumo (in the Movement table) and %KEY_location (in the Location table)

ELSE

the relation is to be created with idUbicacionEntrega (in the Movement table) and %KEY_location (in the Location table)

trickyrelation.png

thank you for any time you can spare in this issue!

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Making relations with different tables based on data from another table

Yes, in the FROM statement put the route to your qvd.

Movement_tmp is the table created in first place. This table must be created after the table Invoice_detail because make a join with that table.

Movement_tmp:

LOAD %KEY_movement,

          %KEY_persona,

          IdUbicacionConsumo,

          IdUbicacionEntrega

From XXXXX;

Left Join

LOAD %KEY_movement,

          %KEY_product

Resident Invoice_detail;

Later create the final table Movement. I forgot to put sentence "NoConcatenate".

Movement:

NoConcatenate

LOAD %KEY_movement,

          %KEY_persona,

          If(%KEY_product = 1 or %KEY_product = 3,

               IdUbicacionConsumo,

               IdUbicacionEntrega

          ) as  %KEY_location

          IdUbicacionConsumo,

          IdUbicacionEntrega

Resident Movement_tmp;

After all drop temporary table:

DROP Table Movement_tmp;

This must function. Sorry for my bad english.

Luciano.-

5 Replies
Not applicable

Making relations with different tables based on data from another table

Hope this help you:

Movement_tmp:

LOAD %KEY_movement,

          %KEY_persona,

          IdUbicacionConsumo,

          IdUbicacionEntrega

From XXXXX;

Left Join

LOAD %KEY_movement,

          %KEY_product

Resident Invoice_detail;

Movement:

LOAD %KEY_movement,

          %KEY_persona,

          If(%KEY_product = 1 or %KEY_product = 3,

               IdUbicacionConsumo,

               IdUbicacionEntrega

          ) as  %KEY_location

          IdUbicacionConsumo,

          IdUbicacionEntrega

Resident Movement_tmp;

DROP Table Movement_tmp;

Regards.-

Not applicable

Making relations with different tables based on data from another table

The script cannot find the Movement_tmp, and in the FROM statement do i have to have the route where te qvd is located?

Not applicable

Making relations with different tables based on data from another table

Yes, in the FROM statement put the route to your qvd.

Movement_tmp is the table created in first place. This table must be created after the table Invoice_detail because make a join with that table.

Movement_tmp:

LOAD %KEY_movement,

          %KEY_persona,

          IdUbicacionConsumo,

          IdUbicacionEntrega

From XXXXX;

Left Join

LOAD %KEY_movement,

          %KEY_product

Resident Invoice_detail;

Later create the final table Movement. I forgot to put sentence "NoConcatenate".

Movement:

NoConcatenate

LOAD %KEY_movement,

          %KEY_persona,

          If(%KEY_product = 1 or %KEY_product = 3,

               IdUbicacionConsumo,

               IdUbicacionEntrega

          ) as  %KEY_location

          IdUbicacionConsumo,

          IdUbicacionEntrega

Resident Movement_tmp;

After all drop temporary table:

DROP Table Movement_tmp;

This must function. Sorry for my bad english.

Luciano.-

Not applicable

Making relations with different tables based on data from another table

Hi Juan Manuel, could you solve your problems? In this case please mark this post as answered, because help other people who can have the same problem.

If not solve your issue continue trying to help.

Regards.-

Not applicable

Making relations with different tables based on data from another table

Yeah!

Thank you very much for your time Luciano it worked like a charm as soon as I understood the logic behind it!

sorry for not selecting the answer as correct sooner, have had some internet connectivity problems, and thought that I had already done it...

thanks again and bye!

Community Browser