Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Straight Table Count Distinct - Slowly Changing Dimension

I was able to find a previous post (link below) that was exactly what I am trying to solve but my current outcome is not the result that I am after. The goal is to only highlight Distinct ID's with the max month. A month number was created to help get the max month ([Month#] = NUM(MakeDate(2020,6)) > one was also created for July > NUM(MakeDate(2020,7))). 

Problem is that when I drop this into a straight table, not all of the Emp_ID's will be distinct. Running out of ideas on how to trouble shoot this so any advice will be appreciated. Here is an expression that I am using to isolate just the max month: =Count({<Month#={$(=max([Month#]))}>}DISTINCT [Emp_ID])

Emp_IDDateMonth#Status
1Jun-2043983Reject
1Jul-2044013Approved
2Jul-2044013Reject
3Jul-2044013Reject
4Jun-2043983Reject
4Jul-2044013Approved
5Jul-2044013Reject

 

Below is the target output

Emp_IDDateMonth#Status
1Jul-2044013Approved
2Jul-2044013Reject
3Jul-2044013Reject
4Jul-2044013Approved
5Jul-2044013Reject

https://community.qlik.com/t5/QlikView-App-Development/Count-items-with-max-date/m-p/292293

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

@Kush I wanted to provide an update. The problem was that I had a number of different tables stitched together to create the table I am referencing. After doing some detective work, I was able to isolate the problem and work this out. 

Once again, so much gratitude for your support. Thank you!

View solution in original post

9 Replies
Highlighted
MVP
MVP

T1:
LOAD
    Emp_ID,
    Date#("Date",'MMM-YY') as Date,
    Month#,
    Status
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

create Straight table

Dimension:
Emp_ID

Expressions:
1) =FirstSortedValue(Date,-Date)  ----> Date
2) =FirstSortedValue(Month#,-Date) -----> Month#
3) =FirstSortedValue(Status,-Date)  -----> Status

 

Highlighted
Creator II
Creator II

First convert Month# into Date format, currently its in Number format. consider max date group by Emp_ID

Highlighted
Partner
Partner

@Kush  Thank you for your support. I recreated my sample data table and both your expression solutions + my original expression (=Count({<Month#={$(=max([Month#]))}>}DISTINCT [Emp_ID])) worked perfectly. Problem is that when I apply it to my true application, I am still running into the same problem. 

My data load has multiple different months. This specific table reference is to  only load July 2020 data. However the June 2020 data is leaking through. I did some detective work and discovered that the table is showing the June 2020 data as July 2020. This has me confused because I created specific references for each month:(Ex: MonthName(MakeDate(2020,6))) AS [Date] / Num(MakeDate(2020,6)) AS [Month#] )

Have you ever encountered this? The June 2020 data is coming through as July 2020? How would you approach this? 

Emp_IDDateMonth#Status
1Jun-2043983Reject
1Jul-2044013Approved
2Jul-2044013Reject
3Jul-2044013Reject
4Jun-2043983Reject
4Jul-2044013Approved
5Jul-2044013Reject
Highlighted
MVP
MVP

How month is calculated?

Highlighted
Partner
Partner

@Kush month is calculated with the following: 

- Date = MonthName(MakeDate(2020,6))

- Month# = Num(MakeDate(2020,6)) 

Each new data load for the month has the above listed updated to the corresponding month (ex: July 2020 data was updated to reflect Date / Month# = 2020,7

Highlighted
MVP
MVP

It looks like month calculation is getting wrong somewhere then. Can you regenerate the data for specific month and replace it and then check how it behaves

Highlighted
Partner
Partner

@Kush  I am looking to see if there are some data load/scripting issues. Just want to thank you for your responses and support. Appreciate you! 

Highlighted
Partner
Partner

@Kush I wanted to provide an update. The problem was that I had a number of different tables stitched together to create the table I am referencing. After doing some detective work, I was able to isolate the problem and work this out. 

Once again, so much gratitude for your support. Thank you!

View solution in original post

Highlighted
MVP
MVP

Glad that you were able to sort it out and it worked. Please mark the appropriate answers correct so that it will be helpful for other