Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
win_anthony
Partner - Contributor III
Partner - Contributor III

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

Labels (4)
1 Solution

Accepted Solutions
win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@Kushal_Chawda 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
Kushal_Chawda

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

 

Anonymous
Not applicable

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

win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@Kushal_Chawda  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
Kushal_Chawda

How month is calculated?

win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@Kushal_Chawda 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

Kushal_Chawda

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

win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@Kushal_Chawda  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! 

win_anthony
Partner - Contributor III
Partner - Contributor III
Author

@Kushal_Chawda 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!

Kushal_Chawda

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