Help: Showing multiple related data in a row

[#1]

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!

[Gysbert_Wassenaar]

See attached qvw. Is that an acceptable solution?

[MarcoWedel]

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

[#4]

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

[#5]

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.

[Gysbert_Wassenaar]

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?

[MarcoWedel]

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

[#8]

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?

[#9]

Thanks for confirmation!

[Gysbert_Wassenaar]

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.