Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Join two tables into other

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?

6 Replies
Highlighted
Not applicable

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;

Highlighted
Not applicable

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;

Highlighted
Not applicable

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

Highlighted

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

Great dreamer's dreams never fulfilled, they are always transcended.
Highlighted

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.

Highlighted

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