Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bvssudhakar
Contributor II

Problem with straight table for multiple date columns

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

1 Solution

Accepted Solutions
pooja_prabhu_n
Contributor III

Re: Problem with straight table for multiple date columns

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

View solution in original post

16 Replies
vishsaggi
Esteemed Contributor III

Re: Problem with straight table for multiple date columns

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.

bvssudhakar
Contributor II

Re: Problem with straight table for multiple date columns

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

Capture.JPGCapture1.JPG

Capture2.JPG

pooja_prabhu_n
Contributor III

Re: Problem with straight table for multiple date columns

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

bvssudhakar
Contributor II

Re: Problem with straight table for multiple date columns

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

bvssudhakar
Contributor II

Re: Problem with straight table for multiple date columns

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

Thank you in advance

pooja_prabhu_n
Contributor III

Re: Problem with straight table for multiple date columns

Hi,

I have modified the script .

PFA qvw file.

bvssudhakar
Contributor II

Re: Problem with straight table for multiple date columns

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

Frank_Hartmann
Honored Contributor II

Re: Problem with straight table for multiple date columns

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)

pooja_prabhu_n
Contributor III

Re: Problem with straight table for multiple date columns

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

View solution in original post