Loops in joing tables

[Anonymous]

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.

[Anonymous]

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!

[jschrader]

Join (WorkingDays):

LOAD

  Distributor_Id,

  Work_schedule

  FROM

$(DataPath)WorkShedule.xls

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

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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!

[Anonymous]

Not yet... Add this in the end:

DROP FIELDS Distributor_Id, Дата FROM Link_Table;

[Anonymous]

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?

[Anonymous]

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