topic Re: How to link 2 date fields into 1 month field in QlikView

How to link 2 date fields into 1 month field

Fri, 24 Jun 2016 04:20:55 GMT

Hi qlikers,

I am stuck at this.

I have one master table and a transaction table.

Suppose my master table has 100 records for the month of May. With primary key as DoctorId. There is a start date and end date in masters. For the month of May the start date will be 01/05/2016 and end date will be 31/05/2016.

Now in my transaction table suppose there are 500 records for the month of may. And there is a transaction date for each record. But now if only 70 out of 100 DoctorIds are present in transaction table.

In my chart i have the dimension MonthName of transaction date, and in my expression i want to calculate percentage like 500/100. But when i take sum.of tranx ids / sum of doctor ids. I get 500/70 for may. The remaining 30 go under null part in the chart as they doesnt have a transaction date as they dont appear in transaction and are only in master.

Regards,

Pratik

Re: How to link 2 date fields into 1 month field

sunny_talwar — Fri, 24 Jun 2016 04:23:35 GMT

How about this:

Sum(TransactionID)/Sum(TOTAL DoctorID)

Re: How to link 2 date fields into 1 month field

Fri, 24 Jun 2016 04:41:26 GMT

Hi Sunny,

Thanks for the quick response. But I cannot use TOTAL as this can be for Multiple Months like,

if there is 3 months data available for Apr May and June then total will give overall total for all the months and this output will be applied for each month in chart.

I want Apr months DoctorIDs count of Apr, Mays for may and so on.

Regards,

Pratik

Re: How to link 2 date fields into 1 month field

sunny_talwar — Fri, 24 Jun 2016 05:08:54 GMT

Difficult to understand what you are trying to do. Can you may be share a sample?

Re: How to link 2 date fields into 1 month field

Fri, 24 Jun 2016 05:42:23 GMT

I cannot upload the qvw but I will paste the script here:

Master:
LOAD * Inline [
Docid,Start_Date,End_Date
1,01/04/2016,30/04/2016
2,01/04/2016,30/04/2016
3,01/04/2016,30/04/2016
4,01/04/2016,30/04/2016
5,01/05/2016,31/05/2016
6,01/05/2016,31/05/2016
7,01/05/2016,31/05/2016
];

Trnx:
LOAD * Inline [
Trnxid,Docid,TrnxDate
1,1,02/04/2016
2,1,03/04/2016
3,1,10/04/2016
4,1,20/04/2016
5,2,21/04/2016
6,2,25/04/2016
7,2,29/04/2016
8,5,04/05/2016
9,5,10/05/2016
10,5,20/05/2016
];

If you reload the dashboard and Take a bar chart with dimension as =MonthName(TrnxDate) and Expression as Count(DISTINCT Docid) ,

we get 2 count for april, 1 for May and 4 fall under null.

The output here should be 4 for the month of April and 3 for the month of May.

I cannot change this dimension as it will be further used to drill down to see weekly trends.

Can you suggest any script or expression level changes to achieve this output

Regards,

Pratik

Re: How to link 2 date fields into 1 month field

sunny_talwar — Fri, 24 Jun 2016 05:50:30 GMT

Will a particular Docid only going to spam a single month in the Master Table??

Re: How to link 2 date fields into 1 month field

sunny_talwar — Fri, 24 Jun 2016 05:58:52 GMT

May be this:

Master:

LOAD * Inline [

Docid,Start_Date,End_Date

1,01/04/2016,30/04/2016

2,01/04/2016,30/04/2016

3,01/04/2016,30/04/2016

4,01/04/2016,30/04/2016

5,01/05/2016,31/05/2016

6,01/05/2016,31/05/2016

7,01/05/2016,31/05/2016

];

Trnx:

LOAD * Inline [

Trnxid,Docid,TrnxDate

1,1,02/04/2016

2,1,03/04/2016

3,1,10/04/2016

4,1,20/04/2016

5,2,21/04/2016

6,2,25/04/2016

7,2,29/04/2016

8,5,04/05/2016

9,5,10/05/2016

10,5,20/05/2016

];

Join (Trnx)

IntervalMatch(TrnxDate, Docid)

LOAD Start_Date,

End_Date,

Docid

Resident Master;

Join (Trnx)

LOAD *

Resident Master;

FinalTable:

LOAD Trnxid,

Docid,

Date(If(Len(Trim(TrnxDate)) = 0, MonthStart(Start_Date), TrnxDate)) as TrnxDate

Resident Trnx;

DROP Table Trnx;

Re: How to link 2 date fields into 1 month field

Anonymous — Fri, 24 Jun 2016 06:37:07 GMT

HI Patrik,

Why don't use InterlvalMatch? You will get all transactions with only one date.

Regards!

Re: How to link 2 date fields into 1 month field

qliksus — Tue, 28 Jun 2016 02:10:28 GMT

You can also use Linktable for your case

Script:

Master:
LOAD *,Docid2 as Key,TextBetween([Start_Date],'/','/')as Mas_Mon Inline [
Docid2,Start_Date,End_Date
1,01/04/2016,30/04/2016
2,01/04/2016,30/04/2016
3,01/04/2016,30/04/2016
4,01/04/2016,30/04/2016
5,01/05/2016,31/05/2016
6,01/05/2016,31/05/2016
7,01/05/2016,31/05/2016
];

Note: Use some monthfunction instead of textbetween

Trnx:
LOAD *,Docid1 as Key,TextBetween(TrnxDate,'/','/')as Trn_Mon Inline [
Trnxid,Docid1,TrnxDate
1,1,02/04/2016
2,1,03/04/2016
3,1,10/04/2016
4,1,20/04/2016
5,2,21/04/2016
6,2,25/04/2016
7,2,29/04/2016
8,5,04/05/2016
9,5,10/05/2016
10,5,20/05/2016
];

LinkTable:
load Distinct
      Docid2 as Key,
      Docid2 as Key1,
      Mas_Mon as Mon1
Resident Master;

load Distinct
      Docid1 as Key,
      Docid1 as Key1,
        Trn_Mon as Mon1
Resident Trnx;