topic Re: Loops in joing tables in QlikView

Loops in joing tables

Anonymous — Mon, 05 Jan 2015 18:53:04 GMT

Good day!

in lscript i'm trying to load tables:

WorkingDays:

LOAD

Work_schedule,

Дата,

Status

FROM

$(DataPath)WorkingDays.xls

(biff, embedded labels, table is [WDays$])

where Дата < today(1);

WorkingShedule:

LOAD

Distributor_Id,

Work_schedule

FROM

$(DataPath)WorkShedule.xls

(biff, embedded labels, table is [WShedule$]);

Also i have link_ table, which have key fields:

Дата,

Distributor_Id

When i do reload model, i see message with error:

Structure of model:

How i can make join between tables WorkingShedule, WorkingDays, Link_table without having loops in database structure?

Source files of tables WorkingShedule, WorkingDays are in attached files

Thanks.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 19:19:54 GMT

try dropping WorkingShedule table at the end of your script.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 19:32:44 GMT

ok. and where i will see data from table WorkingShedule after reload ?

Re: Loops in joing tables

sunny_talwar — Mon, 05 Jan 2015 19:44:45 GMT

If WorkingSchedule have only the two fields: Distributors_Id and Work_schedule then these two are also present in WorkingDays and Link_Table, respectively. But in case you have more than those two fields within WorkingSchedule, I would just rename one of the two fields so that it doesn't create a loop (for example Distributors_Id as WSDistributor_Id). This would break your direct link between WorkingSchedule and Link_Table, but your indirect link will stay intact breaking the loop.

Hopefully this would be helpful.

Best,

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 19:55:10 GMT

thanks, but i can not rename any fields in Work_schedule, because Work_shedule is key field with WorkingDays table and Distributor_Id field also used in others tables

Re: Loops in joing tables

sunny_talwar — Mon, 05 Jan 2015 20:01:17 GMT

Right, but the reason you are seeing the loop is because you WorkingShedule is connected to WorkingDays which is connected to Link_table which is again connected to WorkingShedule. Now the only way to break the loop is to break the link between one of those tables. (All the fields still exists, just one of them have to renamed.

Will you be able to share a qvw file for demonstration purpose?

Best,

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 20:32:03 GMT

Thanks, but have not enough rights to share model... Sorry.((

Re: Loops in joing tables

Mon, 05 Jan 2015 20:36:15 GMT

Hey there,

Can you pls explain how the Link table is created? Maybe that needs to be revisited. Also a sample qvw would help in debugging.

Re: Loops in joing tables

jschrader — Mon, 05 Jan 2015 20:52:57 GMT

Based on the information you have provided, the only way you could have created the link table was to match distribution_id and aata with work_schedule (I need more information if this is not the case). If this is the case, then you join your tables on work_schedule, then you should accomplish the same result while removing the loop. Creating a mapping load with distribution_id and work_schedule would also work.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:07:58 GMT

Maybe all you need is to join WorkingDays and WorkingSchedule into one table.

There will be another issue, synthetic key, because this new table and Link Table have two fields in common - Distributor ID and Data. But this is easy to resolve. For example, create combined key field out of these two fields, and drop Distributor ID and Data from the Link table.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:15:37 GMT

Thanks!!! I also thought about it. But i'd never used join between tables and it's first my experience in load script. Could you please write an example with my task how to do this ?

Best regards!

Re: Loops in joing tables

jschrader — Mon, 05 Jan 2015 21:18:02 GMT

Join (WorkingDays):

LOAD

Distributor_Id,

Work_schedule

FROM

$(DataPath)WorkShedule.xls

(biff, embedded labels, table is [WShedule$]);

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:19:19 GMT

This will work for join:
WorkingDays:
LOAD
Work_schedule,
Дата,
Status
FROM
$(DataPath)WorkingDays.xls
(biff, embedded labels, table is [WDays$])
where Дата < today(1);

JOIN (WorkingDays)
LOAD
Distributor_Id,
Work_schedule
FROM
$(DataPath)WorkShedule.xls
(biff, embedded labels, table is [WShedule$]);

You'll get table WorkingDays with one additional field Distributor_Id.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:23:50 GMT

ok. And how then i must make combined key of Distributor_Id and Дата ?

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:29:18 GMT

Add this after my script:

LEFT JOIN (WorkingDays)
LOAD
Distributor_Id,
Дата,
autonumberhash256(Distributor_Id,Дата) as KeyField
RESIDENT WorkingDays;

Do similar for the Link table, and drop Distributor_Id,Дата from Link.

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:36:26 GMT

WorkingDays:

LOAD

Work_schedule,

Дата,

Status

FROM

$(DataPath)WorkingDays.xls

(biff, embedded labels, table is [WDays$])

where Дата < today(1);

JOIN (WorkingDays)

LOAD

Distributor_Id,

Work_schedule

FROM

$(DataPath)WorkShedule.xls

(biff, embedded labels, table is [WShedule$]);

LEFT JOIN (WorkingDays)

LOAD

Distributor_Id,

Дата,

autonumberhash256(Distributor_Id,Дата) as KeyField

RESIDENT WorkingDays;

LEFT JOIN (Link_Table)

LOAD

Distributor_Id,

Дата,

autonumberhash256(Distributor_Id,Дата) as KeyField

RESIDENT Link_Table;

It's all ?

Thank you!

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:40:06 GMT

Not yet... Add this in the end:

DROP FIELDS Distributor_Id, Дата FROM Link_Table;

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:43:37 GMT

ok..but if i have some others tables , which are also connected with link table by Distributor Id and Дата ?

There i also must make combine keys?

Re: Loops in joing tables

Anonymous — Mon, 05 Jan 2015 21:52:11 GMT

Probably yes. I can't tell for sure without seeing the whole data model.

(If you have multiple tables with the same Distributor Id and Дата fields, it will be quite a mess if you do not combine them into a single key.)