topic Making relations with different tables based on data from another table in QlikView

Making relations with different tables based on data from another table

Wed, 02 May 2012 20:55:04 GMT

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)

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

Making relations with different tables based on data from another table

Wed, 02 May 2012 21:31:25 GMT

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

Making relations with different tables based on data from another table

Wed, 02 May 2012 21:51:58 GMT

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

Making relations with different tables based on data from another table

Wed, 02 May 2012 22:12:16 GMT

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

Making relations with different tables based on data from another table

Fri, 04 May 2012 14:57:29 GMT

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

Making relations with different tables based on data from another table

Sat, 05 May 2012 07:03:24 GMT

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!