topic Re: Problem with straight table for multiple date columns in QlikView

Problem with straight table for multiple date columns

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

Hi Exprets,

Here I am attaching my qvw. in this file i am having multiple date columns for that i have created master calendar for them and also created charts also. But user gave one more requirement.

User wants one Straight table with following fields.

1. Department

2. TitleName

3. jobid

4. No.of postions: (Expression is =Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DateType={'plcdate'}>}CandidateID) )

5. Enter Date

6. Placement Date.

7. Count of Days between Enter Date and placement Date

I am getting the problem (getting '-' only for date fields and count of days fields) to create the straight table with those fields.

Can you guys please check the qvw and help me out to achieve this

Re: Problem with straight table for multiple date columns

vishsaggi — Mon, 17 Sep 2018 14:12:08 GMT

Hi Sudhakar,

You have lot of synthetic keys in your datamodel. You have to fix them first. I have updated the script and attaching your qvw back. Just reload the file and see the data model and also your straight table if it is populating correct dates.

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 06:45:24 GMT

Hi Vishwarath Nagaraju,

I am trying to reload the script these errors i am getting, Can you please check once and let me know why those errors coming

Re: Problem with straight table for multiple date columns

pooja_prabhu_n — Tue, 18 Sep 2018 07:29:37 GMT

Hi,

This is the issue with your data model, dates are not linked properly.

Whenever Enter date is present , Placement date and plc dates are null and vice versa.

So it is not possible to Count of Days between Enter Date and placement Date, because any one of the date is null all the time.

Use canonical dates to link all the dates and then create master calendar

Canonical Date

Please remove synthetic keys in the data model.

Thanks,

Pooja

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 07:36:31 GMT

Hi Pooja Nagaraj,

I have tried with Canonical Date also,

Canonical Date Linking Problem

See that thread and please give some solution because i tried so many solutions but i am not getting proper solution

Thank you in advance

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 07:46:02 GMT

If you want i will share qvd also. please try for solution as per my requirement

Thank you in advance

Re: Problem with straight table for multiple date columns

pooja_prabhu_n — Tue, 18 Sep 2018 08:05:29 GMT

Hi,

I have modified the script .

PFA qvw file.

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 08:42:05 GMT

Hi,

I tried with this script also then that time my all values are changing in charts and text object. Previously all are correct that time i am not able to create only straight table with that requirement. But Now i am getting all wrong values

Can you please check for that also

Thank you in advance

Re: Problem with straight table for multiple date columns

Frank_Hartmann — Tue, 18 Sep 2018 09:44:26 GMT

have a look into pooja´s qvw.

if you supress zeros for placementdate in dimension tab of the straight table in sheet3 you will get the 72 when selecting Year=2018.

in textobject you will need this expression to get the 72:

=Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},Date_Flag={'plcdate'},placementdate={'<>null()'}>}CandidateID)

Re: Problem with straight table for multiple date columns

pooja_prabhu_n — Tue, 18 Sep 2018 09:55:03 GMT

Hi,

I have modified the script. Looks like now the data is matching.

JobEndDate starts from 1/1/1900 12:00:00 AM , hence master calendar has generated year from 1900.

Please find the updated qvw as attached.

Thanks,

Pooja

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 10:07:30 GMT

Hi Frank,

I Understand what you said can you please elaborate that expression. Because I have 5 Text Objects with Different Date_Flag's for every expression we can't null placementdate

E.g.

=Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},Date_Flag={'offerdate'},placementdate={'<>null()'}>}CandidateID)

For this the actual output value is 102 but i am getting some other value

Can you please look for this and give some little more explanation to me

Thank you in advance

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 10:25:01 GMT

Hi Pooja Nagaraj,

I have modified the script and created that straight table. It is working present whatever i have taken the columns for them but i need one small modification (One Expression I want)

I want to one expression for this EntryDate and jobEnd is greater than 01/01/2017

how to write the expression for this and where should i write because i don't want extra column

I want only these 7 columns:

1. Department

2. TitleName

3. jobid

4. No.of postions: (Expression is =Count({<Recruiter={'Irfan Memon','Rajiv Ranjan','Aditi Saxena','Sneha Singh'},DateType={'plcdate'}>}CandidateID) )

5. Enter Date

6. Placement Date.

7. Count of Days between Enter Date and placement Date

Re: Problem with straight table for multiple date columns

pooja_prabhu_n — Tue, 18 Sep 2018 10:32:42 GMT

Hi Sudhakar,

try adding calculated dimension for Enter date like below and check suppress null values.

=if(Date(EnterDate)>'1/1/2017' and Date(JobEndDate)>'1/1/2017',EnterDate)

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 11:02:24 GMT

Hi Pooja Nagaraj,

I have implemented Now it's working fine. Thank you very much for your solution. Because i am trying the solution from very long time.

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 11:03:59 GMT

Hi Frank,

Thank you very much for the support and It's working now with Pooja Nagaraj's solution.

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 11:38:36 GMT

Hi Pooja Nagaraj,

How can we show that master calendar as hierarchy calendar. What should i add in my script to show as hierarchy calendar

Re: Problem with straight table for multiple date columns

bvssudhakar — Tue, 18 Sep 2018 11:40:18 GMT

Hi Frank,

If i want show my master calendar as hierarchy calendar what should i change in my script. Please suggest

Thank you in advance