Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

5 Replies
Not applicable
Author

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
Author

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
Author

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
Author

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
Author

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!