topic Re: Modify and calculate loaded data? in QlikView

Modify and calculate loaded data?

Tue, 22 Apr 2014 13:42:47 GMT

Hi everyone,

I'm a bit new to Qlikview, but I'm wondering if there is possible to work with the data i load, for example, if I load, data from to tables can I then in Qlikview calclulate, ex. amount = 12 and time = 2 and do 12/2 = 6 and then display 6. and so on?

Or do i need to make all calculations, modifications and so before loading it in to qlikview?

thanks in advance!

Re: Modify and calculate loaded data?

alexandros17 — Tue, 22 Apr 2014 13:46:35 GMT

You can do it in Script and in objects in lots of ways

Re: Modify and calculate loaded data?

its_anandrjs — Tue, 22 Apr 2014 13:47:49 GMT

Yes you can do this

LOAD *,amount/time as Division;

LOAD * Inline

[

amount,time

12,2

];

And then take table box and add fields amount , time , Division

and also in front end you can calculate this values there are many ways.

Re: Modify and calculate loaded data?

Tue, 22 Apr 2014 14:02:21 GMT

thx!

just one more thing.. I have timestamps as an integer in minutes after midnight, for example. 432 is 432 minutes after midnight, ( 0.00 ) i need to convert this to time. which in this case will be 07:12

in php i can do it like this:

function tm($nm, $lZ = true){

$mins = $nm % 60;

if($mins == 0) $mins = "0$mins";

$hour = floor($nm / 60);

if($lZ){

if($hour < 10) return "0$hour:$mins";

}

return "$hour:$mins";

}

echo tm(432);

Can i use php scripts in qlikview? or how do i do this?

Re: Modify and calculate loaded data?

Gysbert_Wassenaar — Tue, 22 Apr 2014 14:14:57 GMT

Divide by 60*24 and you'll have the fraction of a full day. You can show that as a time: time(MyTimeStamp/1440,'h:mm')

Re: Modify and calculate loaded data?

Tue, 22 Apr 2014 14:30:43 GMT

where do i use this? as an expression or where do i define that to an variable?

Re: Modify and calculate loaded data?

Gysbert_Wassenaar — Tue, 22 Apr 2014 14:37:04 GMT

Whereever you need it. It's an expression. You can use it to create a new field in the script:

MyTable

load *, time(MyTimeStamp/1440,'h:mm') as MyNewField

from MySource;

Or in a chart expression, or in a variable that you then later use in an expression.

Re: Modify and calculate loaded data?

Thu, 24 Apr 2014 08:37:21 GMT

Hi again!

If i want to use loaded data from two loads in a new load to do a calculation. se example:

timestart:

LOAD

time(P_TIME/1440,'hh:mm') AS timestart,

Resident punches

Where P_TYPE=1;

timeend:

LOAD

time(P_TIME/1440,'hh:mm') AS timeend,

Resident punches

Where P_TYPE=2;

and then i want to take timeend/timestart AS totaltime to se the time lapse.

How do i do this?

Re: Modify and calculate loaded data?

PrashantSangle — Thu, 24 Apr 2014 09:02:10 GMT

Hi,

Looking at your script,

You can do the above thing in one table.

Try this.

In your original table Which is i think punches write below code.

If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) AS timestart,

If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) AS timeend,

Then performed

Load

timeend/timestart AS totaltime

Resident punches

Regards,

Re: Modify and calculate loaded data?

Fri, 25 Apr 2014 11:15:43 GMT

I try this but not succesfull... i do get timeend undefined.

punches:

LOAD

P_DATE AS date,

P_TIME,

P_TYPE,

If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) AS timestart,

If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) AS timeend

FROM

[.....Punches.qvd]

(qvd);

Load

timeend/timestart AS totaltime

Resident punches

dont i need any type of else statement ?

Re: Modify and calculate loaded data?

Fri, 25 Apr 2014 11:21:52 GMT

In simple terms yes you can do it in Qlikview, the question is; "is it the right place to do it". Remember about future proofing your corporate business logic to be product agnostic.

Re: Modify and calculate loaded data?

PrashantSangle — Sat, 26 Apr 2014 14:36:16 GMT

Hi,

can you share your Punches.qvd If possible

then provide sample data to work.

Regards,

Re: Modify and calculate loaded data?

Tue, 06 May 2014 13:25:42 GMT

Hi,

Im still having some troubles..

When i do this load:

punches:

LOAD

P_DATE as date,

P_TYPE,

If(P_TYPE=1,'In') as timestart,

If(P_TYPE=2,'Out') as timeend

FROM

i get this ( se image )

And changing load to:

LOAD

P_DATE as date,

P_TYPE,

If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) as timestart,

If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) as timeend

FROM

i get this ( se image )

I want it to be

date - in - out

in one row, I hope u understand what I mean.?

( the 0 line i dont want to load. )

Re: Modify and calculate loaded data?

Tue, 06 May 2014 13:43:14 GMT

you can try something like below

Table1:

ID, Date, In

Table2:

ID,Date, Out

Join Table1 and Table2

Re: Modify and calculate loaded data?

Tue, 06 May 2014 14:14:25 GMT

can u please show example? where to specify load, from and so on.. thx!

Re: Modify and calculate loaded data?

Tue, 06 May 2014 14:17:28 GMT

There are somany ways of doing this.You can either choose do to in script or in the object itself.

Re: Modify and calculate loaded data?

Tue, 06 May 2014 14:58:07 GMT

You might want to add the NAME or ID to the below loads, because I see multiple records for a date.

TableName:

LOAD

P_DATE as date,

If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) as timestart

FROM

WHERE

P_TYPE=1;

Join(TableName):

LOAD

P_DATE as date,

If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) as timeend

FROM

WHERE

P_TYPE=2;

Re: Modify and calculate loaded data?

Wed, 07 May 2014 12:40:10 GMT

Okey, now i have made some small progress. but still need to get one thing more right..

I have made som selections so i can show u easy. the line with the arrow to the right is the one i want to show, and only that line. ( I guess that there are 2 of each P_TYP=1 and P_TYPE=2 if u look at the result. )

my code is:

timestart:
LOAD
    EMP,
    P_DATE as date,
     If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) as timestart
FROM

WHERE P_TYPE=1;

Join(timestart)
LOAD
    EMP,
    P_DATE as date,
    If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) as timeend
FROM

WHERE P_TYPE=2;

Join(timestart)
LOAD iUserID as EMP,
szName AS namn
FROM

Load
*,
interval(timeend-timestart,'hh:mm') AS workedtime
Resident timestart;

Re: Modify and calculate loaded data?

Wed, 07 May 2014 12:59:16 GMT

I think it is doing a Cartesian join, Modified the second load, can you try this .

Join(timestart)

LOAD

EMP,

P_DATE as date,

If(P_TYPE=1,time(P_TIME/1440,'hh:mm')) as timestart,

If(P_TYPE=2,time(P_TIME/1440,'hh:mm')) as timeend

FROM

WHERE P_TYPE=2;