Join two tables into other

[Anonymous]

Good day.

in load script i load two tables from xlsx files:

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$]);

They have the same field Work_schedule.

Tell me please, how to join these two tables into third (other table) by field Work_schedule and drop tables?

[#2]

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$]);

NOconcatenate

Tbl3:

load * resident WorkingDays;

drop table WorkingDays;

join(Tbl3)

load * resident WorkingShedule;

drop table WorkingShedule;

[#3]

WorkingDaystmp:

LOAD

    Work_schedule,

    Дата,

    Status

FROM

$(DataPath)WorkingDays.xls

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

where Дата < today(1);

OUTER JOIN

WorkingSheduletmp:

LOAD

Work_schedule

  Distributor_Id

  FROM

$(DataPath)WorkShedule.xls

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

finaltable:

load * resident WorkingDaystmp;

outer join

load * resident WorkingSheduletmp;

drop table WorkingDaystmp;

drop table WorkingSheduletmp;

[#4]

in the case you don't drop the tables at the end, use NOconcatenate just before finaltable:

[PrashantSangle]

Hi,

Try like,

WorkingDays:

LOAD

Work_schedule,

Дата,

Status

FROM

$(DataPath)WorkingDays.xls

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

where Дата < today(1);

//WorkingShedule:

join

LOAD

Distributor_Id,

Work_schedule

FROM

$(DataPath)WorkShedule.xls

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

NOconcatenate

Table3:

load * resident WorkingDays;

drop table WorkingDays;

Regards

[Colin-Albert]

You do not need a join.

The two tables have a common field Work_schedule, you can just load each table and QlikView will automatically associate the two tables on the common field name.

You rarely need to use joins in QlikView.

[Colin-Albert]

A better solution would be to use a Mapping Table because the working schedule table only has two columns.

The mapping table is automatically deleted when the script finishes leaving just the WorkingDays table.

WorkingShedule_map:

mapping

LOAD

  Work_schedule,

  Distributor_Id

FROM

$(DataPath)WorkShedule.xls

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

WorkingDays:

LOAD

    Work_schedule,

    ApplyMap('WorkingSchedule_map', Work_schedule) as Distributor,

    Дата,

    Status

FROM

$(DataPath)WorkingDays.xls

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

where Дата < today(1);