Extended programming possible in Qlikview?

[#1]

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,

[Anonymous]

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

[swuehl]

Look into the HELP file for INTERVALMATCH,

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

[#4]

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

[#5]

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

[#6]

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

[swuehl · Accepted solution]

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';

A:

LOAD * INLINE [

date,                value

03may2013,    12

04may2013,    15

05may2013,    12.5

06may2013,    0.5

07may2013,    14.3

];

B:

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);      

[#8]

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