Who can help me adding a column to a table?

[#1]

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.





     

[#2]

Can you provide us with some example data?

[#3]

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.

[Sokkorn]

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

[#5]

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

[#6]

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.

[#7 · Accepted solution]

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;

BR

Hans

[#8]

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

[#9]

All,

Thanks for your help and feedback.