topic Re: Extended programming possible in Qlikview? in QlikView

Extended programming possible in Qlikview?

Wed, 16 Oct 2013 09:05:47 GMT

Dear all,

I'm new in Qlikview and have just followed a Beginner designer training for a first impression.

One of the feature of Qlikview is that you can create a script after selecting "Edit script" from Menubar.

My situation:

Imagine I have a table loaded, called Table A. It contains observations, split by date.

date value

03may2013 12

04may2013 15

05may2013 12.5

06may2013 0.5

07may2013 14.3

Then I have also loaded Table B, it contains observations that are not split by date but has just a begin and end date-time for each row.

Description Start_date End_date

Shutdown 03may2013 07:00 03may2013 10:00

logistic 04may2013 23:00 05may2013 02:30

Merging both tables should result into this:

date value Description Duration_Hours

03may2013 12 Shutdown 3:00

04may2013 15 logistic 1:00

05may2013 12.5 logistic 2:30

06may2013 0.5

07may2013 14.3

As what you may see Table B need to be manupulated on such a way that it can be merged with Table A.

My question:

Is this possible in Qlikview? Are we able to program complex data, or are there limitations?

You comment/experience will be highly appreciated.

With kind reagrds,

Re: Extended programming possible in Qlikview?

Anonymous — Wed, 16 Oct 2013 09:16:23 GMT

Cornelis

Yup, you can is the quick answer.

I'd suggest that first you simply load the 2 data tables in your load script and display then as Table Objects.

Then you may need to ask another question as to what to do next.

Best Regards, Bill

Re: Extended programming possible in Qlikview?

swuehl — Wed, 16 Oct 2013 09:21:11 GMT

Look into the HELP file for INTERVALMATCH,

there is a sample shown that is almost identical to your setting.

Re: Extended programming possible in Qlikview?

Wed, 16 Oct 2013 11:09:17 GMT

HI Swuehl,

I was trying out interval match.

But , it only gets linked for 5th may from table A.So, I was wondering if we could change all the dates from

table A and append 12 pm to it.

Please help me to do that.

I tried to_date(date,'dd/mm/yyyy 12:00 pm') , which did not work.

-Anju

Re: Extended programming possible in Qlikview?

Wed, 16 Oct 2013 11:14:05 GMT

Hi Swuehl,

Thank you for your swift reply!

You are right, this is a good clue, however the column 'Duration_hours' is split over several days.

I suppose more steps is needed to make the table complete.

The data imported is not SQL, just from excel...

Best regards,

Cornelis

Re: Extended programming possible in Qlikview?

Wed, 16 Oct 2013 11:15:35 GMT

Hi Bill,

Thanks for it, it looks encouraging that this is possible.

Indeed, how to do is the next step and a good exercise.

Best regards,

Cornelis

Re: Re: Extended programming possible in Qlikview?

swuehl — Wed, 16 Oct 2013 12:04:40 GMT

Ah, I missed that you need to calculate the duration based on fraction of the days involved in your interval.

Then maybe a different approach is more appropriate:

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET ThousandSep=',';

SET DecimalSep='.';

SET DateFormat = 'DDMMMYYYY';

SET TimestampFormat = 'DDMMMYYYY hh:mm';

SET TimeFormat = 'hh:mm';

LOAD * INLINE [

date, value

03may2013, 12

04may2013, 15

05may2013, 12.5

06may2013, 0.5

07may2013, 14.3

];

RIGHT JOIN LOAD

date(floor(Start_date) + iterno()-1) as date,

interval(if(iterno()=1, if(floor(End_date) > floor(Start_date), 1-frac(Start_date),End_date - Start_date),

if(floor(Start_date)+iterno()-1 = floor(End_date), frac(End_date), 1)))

as Duration

INLINE [

Description, Start_date, End_date

Shutdown, 03may2013 07:00, 03may2013 10:00

logistic, 04may2013 23:00, 05may2013 02:30

] while floor(Start_date) +iterno()-1 <= floor(End_date);

Re: Extended programming possible in Qlikview?

Wed, 16 Oct 2013 12:20:00 GMT

Hi Swuehl,

Thanks for it, it works but it makes me also clear that the script in QV offers various solutions.

A very good learning point!

Best regards,

Cornelis