How to link 2 date fields into 1 month field

[#1]

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

[sunny_talwar]

How about this:

Sum(TransactionID)/Sum(TOTAL DoctorID)

[#3]

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

[sunny_talwar]

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

[#5]

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

[sunny_talwar]

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

[sunny_talwar]

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;

[Anonymous]

HI Patrik,

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

Regards!

[qliksus]

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;