topic Re: Two fields, same table, same data in QlikView

Two fields, same table, same data

ciaran_mcgowan — Thu, 03 Apr 2014 16:21:49 GMT

Hi,

I have a table containing Delivery information and another that contains Warehouse information (like below).

Delivery:

TripNo	PickupLocation	DeliveryLocation	Date
001	South Warehouse	Shop 42	02/04/2014
002	Shop 42	South Warehouse	03/04/2014

Warehouse:

WarehouseNumber	WarehouseName
03	South Warehouse
04	East Warehouse

Each truck brings home a return collection from the shop it delivers to so there are always two rows created for each trip. I had linked the two tables using the warehouse name like this:

Delivery: LOAD PickupLocation AS Warehouse

Warehouse: LOAD WarehouseName AS Warehouse

But if I select 'South Warehouse' link from my list box, it will only show Warehouses that deliveries were pickup up from (ie. Trip 001). What is the best way to accomplish selecting Warehouse from the list box and having it display both PickupLocation & DeliveryLocation (ie. Trip 001 & Trip 002)?

Thanks

Re: Two fields, same table, same data

MK_QSL — Thu, 03 Apr 2014 16:31:37 GMT

Test:

Load

RecNo() as NO,

TripNo,

PickupLocation,

DeliveryLocation,

Date

Inline

[

TripNo, PickupLocation, DeliveryLocation, Date

001, South Warehouse, Shop 42, 02/04/2014

002, Shop 42, South Warehouse, 03/04/2014

];

Warehoues:

Load

NO,

PickupLocation as Warehouse

Resident Test;

Load

NO,

DeliveryLocation as Warehouse

Resident Test;

Load

WarehouseNumber,

WarehouseName as Warehouse

Inline

[

WarehouseNumber, WarehouseName

03, South Warehouse

04, East Warehouse

];

=====================================

Re: Two fields, same table, same data

its_anandrjs — Thu, 03 Apr 2014 16:44:49 GMT

You can load like

Delivery:

LOAD * Inline

[

TripNo, PickupLocation, DeliveryLocation, Date

001, South Warehouse, Shop 42, 02/04/2014

002, Shop 42, South Warehouse, 03/04/2014

];

LOAD RowNo() as Rid, TripNo, PickupLocation as WarehouseName,Date

Resident Delivery;

Concatenate

LOAD RowNo() as Rid,TripNo, DeliveryLocation as WarehouseName,Date

Resident Delivery;

DROP Table Delivery;

Warehouse:

load WarehouseNumber, WarehouseName;

LOAD * Inline

[

WarehouseNumber, WarehouseName

03, South Warehouse

04, East Warehouse

];

Hope this helps

Re: Two fields, same table, same data

maxgro — Thu, 03 Apr 2014 23:21:45 GMT

see attachment

Re: Two fields, same table, same data

ciaran_mcgowan — Fri, 04 Apr 2014 09:39:05 GMT

Hi Manish,

I'm not sure if an inline load is feasible here. The examples above are greatly simplified: I've 21 years worth of deliveries, with 45 Warehouses, with around 316 deliveries per year (158 pickups & 158 deliveries).

Do you know of another way? Thanks.

Re: Two fields, same table, same data

ciaran_mcgowan — Fri, 04 Apr 2014 09:39:49 GMT

Hi Anand,

Same response as to Manish (above),

Do you know of another way? Thanks.

Re: Two fields, same table, same data

ciaran_mcgowan — Fri, 04 Apr 2014 09:40:20 GMT

Hi Massimo,

Same response as to Manish & Anand (above),

Do you know of another way? Thanks.

Re: Two fields, same table, same data

MK_QSL — Fri, 04 Apr 2014 09:58:50 GMT

Inline load is here to use your sample data.

You can definitely use your Direct Database (i.e. Oracle or SQL Server), but believe me, the best solution is to get your requirements using script level work.

At UI level, looking upon your database, will severally affect performance.

Re: Two fields, same table, same data

maxgro — Fri, 04 Apr 2014 10:00:12 GMT

we used (here and in other post) inline load because often is the only way to try before answering

why? because you have the data (qvd, database, etc, .....); we don't

replace inline load of my (or other answers)

Delivery:

LOAD * Inline

[

TripNo, PickupLocation, DeliveryLocation, Date

001, South Warehouse, Shop 42, 02/04/2014

002, Shop 42, South Warehouse, 03/04/2014

];

with your data (this can't work on my pc, so I used inline)

Delivery:

LOAD

TripNo, PickupLocation, DeliveryLocation, Date

from

yourfile.qvd (qvd);

Re: Two fields, same table, same data

MarcoWedel — Mon, 07 Apr 2014 00:09:07 GMT

Hi Ciaran,

another solution:

Warehouse:
LOAD *
FROM [http://community.qlik.com/thread/113097]
(html, codepage is 1252, embedded labels, table is @2);

Delivery:
LOAD *
FROM [http://community.qlik.com/thread/113097]
(html, codepage is 1252, embedded labels, table is @1);

Left Join
LOAD Distinct
  PickupLocation,
  DeliveryLocation,
  AutoNumberHash128(PickupLocation, DeliveryLocation) as %LocKey
Resident Delivery;

tabLinkWarehouse:
LOAD Distinct
  PickupLocation as WarehouseName,
  %LocKey
Resident Delivery;

LOAD Distinct
  DeliveryLocation as WarehouseName,
  %LocKey
Resident Delivery;

regards

Marco

Re: Two fields, same table, same data

ciaran_mcgowan — Mon, 07 Apr 2014 08:40:34 GMT

Hi Marco,

Thank you for your suggestion, I will keep this in mind for the next time I have this problem.

Kind Regards,

Ciarán