Load Script Formatting and calculation

[samuel_brierley]

Hi i Have some data from excel that im trying to put into QlikView. The format isnt really changeable(currently general) as its imported data. i was under the impression that qlikview would recognise this as a date/time and would allow me to use as such e.g finish-start=Duration and so on.

Ive tried in the load script with no avail. any ideas?

Timestamp#(Start_Date, 'DD/MM/YY hh:mm:ss') as [Start Date],
Timestamp#(Finish_Date, 'DD/MM/YY hh:mm:ss') as [Finish Date]

Start_Date	Finish_Date
01/08/13 09:00	03/03/14 17:30
01/08/13 09:00	02/08/13 08:30

[Nicole-Smith]

You don't actually have seconds on your timestamps (from what you've posted anyway), so maybe try:

Timestamp#(Start_Date, 'DD/MM/YY hh:mm') as [Start Date],
Timestamp#(Finish_Date, 'DD/MM/YY hh:mm') as [Finish Date]

[Anonymous · Accepted solution]

Hi,

You could read the data like :

Results_from_Excel:

LOAD Start_Date, Finish_Date FROM (biff, embedded labels, table is Blad1$);

so without any conversion, and for the calculation (in a expression)  you could use the interval-command like :

=Interval( Date(Finish_Date) - Date( Start_Date), 'dd hh:mm:ss'  )

Paul

[sundarakumar]

Hi Samuel,

Load

start as [start date],

end as [end date],

[end date]-[start date] as duration

from source;
the above script will result in error, as Qlikview doesnot know [start date] and [end date]{alias names} until the execution of the load statement is complete

.So try using the bewlow statement,

Load

start as [start date],

end as [end date],

end -start  as duration

from source;

I think this is where you are loosing it...

Hope this helps.

If you want to make some sort of calculations then use resident to achive.

-Sundar

[sundarakumar]

If you want to do some calculation and then use the filed to calculate another calculated field you could use resident load as below,

first:

Load

id,

exp(start) as [start date],

exp(end) as [end date]

from source;

Second:

Load

id,

[start date],

[end date],

[end date]-[start date] as duration

resident first;

drop table first;

Hope this helps...

-Sundar