topic Re: Who can help me adding a column to a table? in QlikView

Who can help me adding a column to a table?

Fri, 04 Nov 2011 10:20:40 GMT

I am loading a table with event information.

The information exist of 2 sorts: Plan and Actual.

EventData:

LOAD [Plan / Actual],

[Stage Type],

[Event Type],

...

FROM ...a.xls

So one event can exist of 2 rows, one for the Plan and then the Actual event.
I like to update the Planning row with information from the Actual row.

So what I tried is to first load the whole table and then load if again using JOIN, but only update the Plan row when I read the Actual row.

Somehow I get many extra rows in my table.

JOIN (EventData)

LOAD

If ([Plan / Actual] = 'Actual' and [Stage Type] = 'Transport' and [Event Type] = 'Arrival', [Actual Time of Arrival]) as ArrivedPhysicaly

FROM ...a.xls

Also tried to put it in 2 tables but somehow do not get it to work. Tried also other stuff which did not work .

I am fairly new to qlikview and hope someone can help me.

Thanks in advance.

Who can help me adding a column to a table?

Fri, 04 Nov 2011 10:32:03 GMT

Can you provide us with some example data?

Who can help me adding a column to a table?

Fri, 04 Nov 2011 10:34:47 GMT

Data looks approx like this:

[Plan/Actual], [Event Type], [Stage Type], [ETA], [Actual Time of Arrival]

Plan, Ship, Transport , 2/11/2011, -

Plan, Arrival, Transport , 2/11/2011, -

Actual, Ship, Transport , 2/11/2011, - 2/11/2011

Actual, Arrival, Transport , 2/11/2011, - 10/11/2011

So the planning type records and the actual type records are in the same table.

I like to keep this in one table.

Re: Who can help me adding a column to a table?

Sokkorn — Fri, 04 Nov 2011 11:35:16 GMT

Hi,

You said that "I like to keep this in one table". By your sample data is in one table already. So I'm not clear what you want. Maybe you try to combine two table together, right? If the answer is yes, then give the sample data for the first and second table to us.

Below just an idea to handle you issue:

EventData:
LOAD [Plan / Actual], 
     [Stage Type], 
     [Event Type],
     IF([Plan / Actual] = 'Actual' AND [Stage Type] = 'Transport' AND [Event Type] = 'Arrival', [Actual Time of Arrival]) AS ArrivedPhysicaly,
     ... 
FROM ...a.xls

If the [Actual Time of Arrival] in the table ...a.xls then no need to use inner join in your script.

Regards,

Sokkorn

Re: Who can help me adding a column to a table?

Fri, 04 Nov 2011 11:58:30 GMT

Hi,

I am aswell not clear how the output should look like. As far as I understand is that you want to implement the actual data to the planing fields if there is an entry for the [Actual Time of Arrival] field of the same event. I would recommend to add a key for every event to handle this problem much easier.

Here is what I used to atleast solve the problem with the multiple tables:

Source:
LOAD * INLINE [
    Plan/Actual, Event Type, Stage Type, ETA, Actual Time of Arrival
    Plan, Ship, Transport, 2/11/2011, -
    Plan, Arrival, Transport, 2/11/2011, -
    Actual, Ship, Transport, 2/11/2011, 2/11/2011
    Actual, Arrival, Transport, 2/11/2011, 10/11/2011
];

Date:
LOAD
[Actual Time of Arrival],
ETA,
[Plan/Actual],
[Event Type],
[Stage Type],
IF([Plan/Actual] = 'Actual' AND [Stage Type] = 'Transport' AND [Event Type] = 'Arrival', [Actual Time of Arrival]) AS ArrivedPhysicaly
Resident Source;
DROP Table Source

Regards,

David

Who can help me adding a column to a table?

Fri, 04 Nov 2011 12:31:41 GMT

Already thanks for your answer, but I am not sure if I understand your answer and I might need to explain my problem again.

I have an excel sheet which contains Planning and Actual records/rows.

So in the excel sheet it states f.e.

Field id, Plan/Actual, Event, Plan date/time, Actual date/time

1234, Plan , Order Burger, 4/11/11 16:00, -

1234, Plan , Receive Burger, 4/11/11 16:10, -

1234, Plan , Eat Burger, 4/11/11 16:15, -

1234, Actual , Order Burger, 4/11/11 16:00, 4/11/11 16:08

1234, Actual , Receive Burger, 4/11/11 16:10, 4/11/11 16:12

4567, Plan , Order Burger, 4/12/11 11:00, -

I load this into qlikview and like to have the following result:

Field id, Plan/Actual, Event, Plan date/time, Actual date/time

1234, Plan , Order Burger, 4/11/11 16:00, 4/11/11 16:08

1234, Plan , Receive Burger, 4/11/11 16:10, 4/11/11 16:12

1234, Plan , Eat Burger, 4/11/11 16:15, -

1234, Actual , Order Burger, 4/11/11 16:00, 4/11/11 16:08

1234, Actual , Receive Burger, 4/11/11 16:10, 4/11/11 16:12

4567, Plan , Order Burger, 4/12/11 11:00, -

This is so that when I take all the Plan rows, I can calculate the time between my Plan and my Actual date/time.

So in other words, I like to update my Plan type rows with the data from a corresponding Actual row.

I hope this explains my issue better.

Who can help me adding a column to a table?

Fri, 04 Nov 2011 13:09:52 GMT

Hi,

In your example, you have the same info on the Actual row?

Why not use that?

You could create a second table whit the Plan and Actual datetime with the field and Event as Key...

If I wanted to add a column as your exampl, I would do something like this:

//First your orig table... Without Actual date/time

EventData:

LOAD [Field id], [Plan/Actual], Event, [Plan date/time] from a.xlsx;

//Then join the Actual date/time using Field id and Event as join fields

left join (EventData)

LOAD [Field id], Event, [Actual date/time] from a.xlsx;

Hans

Who can help me adding a column to a table?

Fri, 04 Nov 2011 13:36:19 GMT

If you really want to use only one table this is what should give you what you want:

Date_temp:

LOAD [Field id],

[Plan/Actual],

[Event Type],

ETA,

[Actual Time of Arrival]

FROM

C:\**.xlsx

(ooxml, embedded labels, table is Table1)

;

Date:

LOAD

[Field id],

[Plan/Actual],

[Event Type],

ETA,

[Actual Time of Arrival],

if([Plan/Actual]='Plan',peek([Actual Time of Arrival])) AS ArrivedPhysicaly

Resident Date_temp Order by [Field id],[Event Type], [Plan/Actual];

DROP Table Date_temp;

But I would highly recommend to work with the mentioned option from Hans to use a second table and link them with a key build of the field id and the event, then you could just load the Actual Date and save it as ArrivedPhysicaly.

Regards

David

Who can help me adding a column to a table?

Mon, 07 Nov 2011 11:54:47 GMT

All,

Thanks for your help and feedback.