topic Re: Help: Showing multiple related data in a row in QlikView

Help: Showing multiple related data in a row

Mon, 26 Oct 2015 14:49:57 GMT

Hello,

My issue is how to show related data per row? Let me explain in an example. For this input:

Shipment:

LOAD * INLINE [

ShipmentID, Qty

1, 10

2, 30

3, 20

];

Event:

LOAD * INLINE [

EventID, ShipmentID, EventDate, EventName

1, 1, '2015-08-01', 'Processed'

2, 2, '2015-09-05', 'Shipped'

3, 3, '2015-10-01', 'Processed'

4, 1, '2015-08-04', 'Shipped'

5, 2, '2015-09-01', 'Processed'

6, 2, '2015-09-09', 'Delivered'

7, 3, '2015-10-03', 'Transit'

8, 3, '2015-10-02', 'Shipped'

9, 3, '2015-10-15', 'Wait'

10, 1, '2015-08-15', 'Delivered'

];

I would like to get this as output (Ev. short for Event):

ShipmentID	Ev.ID_1	Ev.Date_1	Desc_1	Ev.ID_2	Ev.Date_2	Desc_2	Ev.ID_3	Ev.Date_3	Desc_3	Ev.ID_4	Ev.Date_4	Desc_4
1	1	2015-08-01	Processed	4	2015-08-04	Shipped	10	2015-08-15	Delivered
2	5	2015-09-01	Processed	2	2015-09-05	Shipped	6	2015-09-09	Delivered
3	3	2015-10-01	Processed	8	2015-10-02	Shipped	7	2015-10-04	Transit	9	2015-10-15	Wait

I know that I can't have dynamic number of column. Let's say that it's hardcoded to max 4. I will change as needed.

Order in which events should be presented is by date only. ID or Description can't be used for sorting since events are stored in sort of random order, and ID is assigned as they arrive. Also, Description can be more or less any string, but for convenience I used only 5 different.

I would like to have this in pivot table, but it's not that important where it's presented as long as I get it done.

What are my options kind strangers?

Thanks!

Re: Help: Showing multiple related data in a row

Gysbert_Wassenaar — Mon, 26 Oct 2015 18:17:59 GMT

See attached qvw. Is that an acceptable solution?

Re: Help: Showing multiple related data in a row

MarcoWedel — Mon, 26 Oct 2015 23:01:33 GMT

Hi,

if you want to create separate fields for the different events yo ucould try with (using Gysbert's example):

Shipment:

LOAD * Inline [

ShipmentID, Qty

1, 10

2, 30

3, 20

];

EventTemp:

CrossTable(ColNam,ColVal,2)

LOAD ShipmentID,

AutoNumber(EventID,ShipmentID) as Counter,

EventID as Ev.ID,

EventDate as Ev.Date,

EventName as Desc

Inline [

EventID, ShipmentID, EventDate, EventName

1, 1, '2015-08-01', 'Processed'

2, 2, '2015-09-05', 'Shipped'

3, 3, '2015-10-01', 'Processed'

4, 1, '2015-08-04', 'Shipped'

5, 2, '2015-09-01', 'Processed'

6, 2, '2015-09-09', 'Delivered'

7, 3, '2015-10-03', 'Transit'

8, 3, '2015-10-02', 'Shipped'

9, 3, '2015-10-15', 'Wait'

10, 1, '2015-08-15', 'Delivered'

];

Event:

Generic LOAD

ShipmentID,

ColNam&'_'&Counter,

ColVal

Resident EventTemp;

DROP Table EventTemp;

hope this helps

regards

Marco

Re: Help: Showing multiple related data in a row

Wed, 28 Oct 2015 14:38:55 GMT

Hello Gysbert, thanks for helping me, I really appreciate it. I'm trying to implement your approach, and I'm having trouble understanding something in the code:

LOAD *, ApplyMap('MapQty',ShipmentID) as Qty, AutoNumber(EventID,ShipmentID) as Counter

is there some significance in using ApplyMap(...) as Qty? Why is it called the same as the field in shipment table? That Qty in shipment is not unique, maybe my data sample is not 100% clear.

Thanks

Re: Help: Showing multiple related data in a row

Wed, 28 Oct 2015 14:41:31 GMT

Hi Marco, thanks for helping me. Your solution is interesting, but how will it scale once I have 100k shipments with 500k events? I'm not sure that I understand properly.

Right now I'm trying both approaches. Even though it clearly works on sample data I provided, I'm trying now to plug in into real data, and it tricky. I will try to put together better example and accompanied .qvw doc.

Re: Help: Showing multiple related data in a row

Gysbert_Wassenaar — Wed, 28 Oct 2015 16:41:01 GMT

That Qty in shipment is not unique

You mean that you have several rows in the Shipment table with the same shipment id, but different Qty values?

Re: Help: Showing multiple related data in a row

MarcoWedel — Thu, 29 Oct 2015 01:06:52 GMT

Hi,

attached an example with 100k ShipmentIDs.

Script didn't perform too well, I have to admit ...

Shipment:
LOAD RecNo() as ShipmentID,
     Ceil(Rand()*1000) as Qty
AutoGenerate 100000;

EventTempTemp:
LOAD RowNo() as EventID,
     RecNo() as ShipmentID,
     Date(MakeDate(2010)+Ceil(Rand()*(Today()-MakeDate(2010)))) as EventDate,
     Pick(Ceil(Rand()*5),'Processed','Shipped','Delivered','Transit','Wait') as EventName
AutoGenerate 100000
While IterNo()<=Ceil(Rand()*10);

EventTemp:
CrossTable(ColNam,ColVal,2)
LOAD ShipmentID,
     AutoNumber(EventID,ShipmentID) as Counter,
     EventID as Ev.ID,
     EventDate as Ev.Date,
     EventName as Desc
Resident EventTempTemp;

DROP Table EventTempTemp;

Event:
Generic LOAD
  ShipmentID,
  ColNam&'_'&Counter,
  ColVal
Resident EventTemp;

DROP Table EventTemp;

hope it might help nevertheless

regards

Marco

Re: Help: Showing multiple related data in a row

Thu, 29 Oct 2015 14:56:46 GMT

No no, I mean I can have several rows with different shipment_id and with same Qty.

I don't understand this part in the script:

LOAD *, ApplyMap('MapQty',ShipmentID) as Qty, AutoNumber(EventID,ShipmentID) as Counter

Why is the ApplyMap called Qty, same as field in the shipment table? It that a trick to force QV to link something?

Re: Help: Showing multiple related data in a row

Thu, 29 Oct 2015 14:57:41 GMT

Thanks for confirmation!

Re: Help: Showing multiple related data in a row

Gysbert_Wassenaar — Fri, 30 Oct 2015 05:40:30 GMT

No, that's my trick. You can give the new field any name you want. I merely choose Qty because you already had a field called that so it would make sense to give the new field the same name since it will contain the information from the Qty field of the mapping table. A mapping table is discarded at the end of the load so there's only one Qty field at the end. You can consider a mapping table as nothing more then a temporary lookup table for a search&replace operation.

Re: Help: Showing multiple related data in a row

Thu, 19 Nov 2015 13:07:19 GMT

Hi guys,

I was on vacation and I didn't have time to check your solutions in detail yet. Just wanted to say thank you again, and I will close this question as soon as I confirm that I solved my case. Your help is greatly appreciated!