topic Re: Link with Master Calendar in QlikView

Link with Master Calendar

bvssudhakar — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

Here I am attaching my qvd and qvw files, Can you guys please help me out for the below mentioned challenge:

I have a multiple date columns in my file. Now i want to create master hierarchy calendar for and link with all the dates. because i want to show only one list box in my report i.e. hierarchy calendar list box. For that i wrote some script but i was strucked somewhere

Output scenario:

I want to create a text object for joiners

for that expression is like this: Count(candidateID) but these candidate id's count are based on plcdate column.

If i select April Month and 2018 year from hierarchy calendar list box output should be 15 for this text object.

Please Help me out from this guys.

mbaeyens‌manoj217‌rwunderlich‌franky_h79‌priyalvp24‌swuehl‌petter-s‌trdandamudi‌stalwar1‌loveisfail‌

Thank you in advance

Re: Link with Master Calendar

m_woolf — Wed, 05 Sep 2018 11:47:29 GMT

Search Recipes | Qlikview Cookbook

Re: Link with Master Calendar

bvssudhakar — Wed, 05 Sep 2018 12:01:40 GMT

Hi M W,

There He is having three different tables, Here i have only one table in that table multiple date columns are there.

If possible can you please try with my qvd by creating text object whatever i mentioned in my question

Thank you in advance

Re: Link with Master Calendar

rajivmeher — Wed, 05 Sep 2018 13:40:16 GMT

Hi bvssudhakar‌

Please see the attached and suggest if this works.

Regards

Rajiv.

Re: Link with Master Calendar

bvssudhakar — Wed, 05 Sep 2018 14:09:43 GMT

Hi Rajiv,

Have you created text object?? and how you create the only one list box for all date

Because I am using Qlikview Personal edition, So i am not able to open that file but by using qliksense i have written script but i want to show text object also

If create Can you please explain here

Re: Link with Master Calendar

rajivmeher — Wed, 05 Sep 2018 14:30:19 GMT

Hi bvssudhakar‌

I have used the same model that you have shared. I am attaching the script for the model. Please use it in your model and reload (I have changed the qvd location as per my machine, just change it to yours.

Regards

Rajiv.

Re: Link with Master Calendar

bvssudhakar — Wed, 05 Sep 2018 14:41:18 GMT

Hi Rajiv,

I have implemented this in my qvw. but we can't show these many date list boxes na?? user is asking he wants only three list boxes, those are year, month,quarter list boxes only. if possible he wants only one hierarchy list box.

Can you please give me the suggestions for this

Re: Link with Master Calendar

rajivmeher — Wed, 05 Sep 2018 15:00:58 GMT

Hi bvssudhakar‌

Just want to make sure I understand the requirement properly. You need to show the count for id by filtering it on Year, Month and Quarter right? How do you want to differentiate it as Placed, Placement, Enter etc? Do you want to show the values in different textboxes, such as the values changes in different text boxes based on the Year, Month etc selection?

Regards

Rajiv.

Re: Link with Master Calendar

bvssudhakar — Wed, 05 Sep 2018 15:03:41 GMT

hic‌

Can you please help me out for this.

Re: Link with Master Calendar

rwunderlich — Wed, 05 Sep 2018 15:28:16 GMT

The cookbook example should still work for you. You have multiple dates, just load each date in turn into the DateLink table.

-Rob

Re: Link with Master Calendar

priyalvp24 — Thu, 06 Sep 2018 06:15:08 GMT

Kindly send the qvf

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 06:24:00 GMT

Hi Priyal

I already attached qvd file with the discussion. Please find and help me out from this

Re: Link with Master Calendar

priyalvp24 — Thu, 06 Sep 2018 06:28:50 GMT

Not able to find it,please resend it

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 06:36:38 GMT

Hi Rajiv and rwunderlich‌,

My Requirement is: I want create 4 text objects and one list box i.e. Hierarchy calendar

And The 4 text object Expressions are like this way:

Text object1 : =Count({<Recruiter={'Irfan','Ranjan','Saxena','Singh'}>}CandidateID) and this will relationship with master calendar and plc date column

Text object2 : =Count({<Recruiter={'Irfan','Ranjan','Saxena','Singh'}>}CandidateID) and this will relationship with master calendar and offer date column

Text object3 : =Count({<Recruiter={'Irfan','Ranjan','Saxena','Singh'}>}CandidateID) and this will relationship with master calendar and Decline date column

Text object4 : =Count({<Recruiter={'Irfan','Ranjan','Saxena','Singh'}>}CandidateID) and this will relationship with master calendar and JobEnd date column

How to do that can you guys help me,

And I asked my PowerBI Team also, They Implemented like this:

for text object1:

Placed =

CALCULATE (

Count (PlacementDate[CandidateID] ),

USERELATIONSHIP (

PlacementDate[plcdate],

'Calendar'[Date]

)

for text object2:

Placed =

CALCULATE (

Count (PlacementDate[CandidateID] ),

USERELATIONSHIP (

PlacementDate[Offerdate],

'Calendar'[Date]

)

.......... like this

Note: PlacementDate, Calendar are Table Names...., CandidateID,plcdate,date are Column Names

And the out came correctly for all of them

But we want to implement it Qlikview or Qliksense.

I think now you people got the clarity on requirement

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 06:39:28 GMT

Hi Priyal,

Here, I am attaching again... and see my latest reply also I gave my requirement clearly.

Thank you in advance

Re: Link with Master Calendar

priyalvp24 — Thu, 06 Sep 2018 06:51:21 GMT

Hi,

Use resident load and concatenation having the date field as the same column names as in master calender.

Load

field1,

field2

datefield1 as DATE_FILELD,

datefield2,

datefield3,

datefield4

from qvd;

concatenate(a)

load

datefield2 as DATE_FILELD

resident a;

concatenate(a)

load

datefield3 as DATE_FILELD

resident a;

concatenate(a)

load

datefield4as DATE_FILELD

resident a;

Link this DATE_FILELD with master calender

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 07:22:41 GMT

Hi priyalvp24‌,

How to link with master calendar, Can you Please Explain me.

If possible can you please do with my qvd & qvw

I tried So many ways but when i am trying to create text objects i am not able to get the correct answer

Re: Link with Master Calendar

priyalvp24 — Thu, 06 Sep 2018 07:48:34 GMT

will send you the qvf

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 08:19:59 GMT

Thank you in advance,

I will wait for your qvf

Re: Link with Master Calendar

bvssudhakar — Thu, 06 Sep 2018 09:24:50 GMT

petter-s‌marcowedel‌

Guys can you please help me out from this