topic Join two tables into other in QlikView

Join two tables into other

Anonymous — Tue, 06 Jan 2015 07:14:26 GMT

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?

Re: Join two tables into other

Tue, 06 Jan 2015 07:23:50 GMT

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;

Re: Join two tables into other

Tue, 06 Jan 2015 07:28:23 GMT

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;

Re: Join two tables into other

Tue, 06 Jan 2015 07:30:02 GMT

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

Re: Join two tables into other

PrashantSangle — Tue, 06 Jan 2015 07:31:59 GMT

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

Re: Join two tables into other

Colin-Albert — Tue, 06 Jan 2015 07:41:41 GMT

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.

Re: Join two tables into other

Colin-Albert — Tue, 06 Jan 2015 07:45:03 GMT

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