Convert List of Timestamps and Events to Table

[Schnuber]

Hi everyone,

i like to convert a list of Order-IDs with corresponding Events and Event-Timestamps to a Table View like in this example.

FROM:	Order-ID	Event-Timestamp	Event-Type
	1	16.05.20 17:35	Request
	2	16.05.20 17:50	Request
	1	17.05.20 07:50	Accepted
	3	17.05.20 08:00	Request
	2	17.05.20 08:05	Accepted
	1	17.05.20 08:15	Send
	3	17.05.20 08:33	Accepted
	3	17.05.20 09:12	Send
	2	17.05.20 11:03	Send
TO:	Order-ID	Event-Type: Request	Event-Type: Accepted	Event-Type: Send
	1	16.05.20 17:35	17.05.20 07:50	17.05.20 08:15
	2	16.05.20 17:50	17.05.20 08:05	17.05.20 09:12
	3	17.05.20 08:00	17.05.20 08:33	17.05.20 11:03

Can you help me with that?

I am new to Qlik. Please excuse if this is easy to solve or an RTFM question for which I have not found the right chapter. 🙂

[marcel_olmo · Accepted solution]

Hi @Schnuber ,

here you have the code :

FromOrders:

LOAD * INLINE [

FROM:, Order-ID, Event-Timestamp, Event-Type,
, 1, 16.05.20 17:35, Request,
, 2, 16.05.20 17:50, Request,
, 1, 17.05.20 07:50, Accepted,
, 3, 17.05.20 08:00, Request,
, 2, 17.05.20 08:05, Accepted,
, 1, 17.05.20 08:15, Send,
, 3, 17.05.20 08:33, Accepted,
, 3, 17.05.20 09:12, Send,
, 2, 17.05.20 11:03, Send
];

store FromOrders into FromOrders.qvd;

drop Table FromOrders;

ToOrders:

load Distinct

'' as "TO:",
"Order-ID" as "Order-ID",
"Event-Timestamp" as "Event-Type: Request"
FROM
[FromOrders.qvd]
(qvd)

Where "Event-Type" = 'Request';

join

load Distinct

'' as "TO:",
"Order-ID" as "Order-ID",
"Event-Timestamp" as "Event-Type: Accepted"
FROM
[FromOrders.qvd]
(qvd)

Where "Event-Type" = 'Accepted';

join

load Distinct

'' as "TO:",
"Order-ID" as "Order-ID",
"Event-Timestamp" as "Event-Type: Send"
FROM
[FromOrders.qvd]
(qvd)

Where "Event-Type" = 'Send';

Best regards, Marcel.