topic Help with2 diffenrent Dates matching in QlikView

Help with2 diffenrent Dates matching

didierodayo — Wed, 20 Jul 2016 08:40:06 GMT

Hello,

I have 2 table with the following headers. Note: a student can graduate many times and a student can have many certificates.

TABLE1

STUDENT_ID ; GRADUATION_DT

TABLE2

STUDENT_ID ; CERTIFICATE_DT

The date are both in the format DD/MMM/YYYY. I need help creating a line chart with the following rules:

1- Show the line in Green where there is a certificate date and a matching graduation date

2- IF there is a certificate date and no matching graduation date I would like the line to drop or something(??? )

what is the best way to represent this by playing with the dates?

Thanks a lot for your assistance.

Re: Help with2 diffenrent Dates matching

Digvijay_Singh — Wed, 20 Jul 2016 09:15:30 GMT

Can you share dimension you are expecting and the sample chart.

Re: Help with2 diffenrent Dates matching

avinashelite — Wed, 20 Jul 2016 09:22:02 GMT

Please share the dimension and the sample data so that we can suggest the solution

Re: Help with2 diffenrent Dates matching

Anonymous — Wed, 20 Jul 2016 09:26:54 GMT

I would join the tables, create flags for each case and use set analysis

left join (TABLE1)

load *

resident TABLE2;

FinalFact:

Load *,

if (GRADUATION_DT=CERTIFICATE_DT,1,0) as FlgGr_Ce, // if you Need equal Dates, otherwise test on not null

If (not is null(GRADUATION_DT) and not is null(CERTIFICATE_DT,1,0) as FlgGR

resident TABLE1;

Then you can define a line Chart with STUDENT_ID as Dimension and e.g. Count({<FlgGr_Ce={1}>}GRADUATION_DT for green line and Count({<FlgGr={1}>}GRADUATION_DT for other line

please specify how your Chart should look like (dimensios, expressions)

Re: Help with2 diffenrent Dates matching

didierodayo — Wed, 20 Jul 2016 09:41:44 GMT

Hi,

I would like a result similar to this where the dimension is the student ID I would be counting the number of certificates in a month and the dots on the line would tell me if there was a matching GRADUATION_DT or not.

Thanks for your help.

Didier

Re: Help with2 diffenrent Dates matching

didierodayo — Wed, 20 Jul 2016 09:42:39 GMT

Hi Rudolf ,

I am going to try your result now and let you know.

Thanks

Re: Help with2 diffenrent Dates matching

Wed, 20 Jul 2016 10:11:09 GMT

Hi Didier,

To implement this, you can use a combo chart. Add student id as 1st dimension and month as 2nd dimension.

1st expression as count(certificarion date) and show it in bar. For 2nd expression create a flag in script as mentioned by Rudulf in above post and write expression as count({<flag={'1'}>certification date}). display 2nd expression as line in chart.