topic Re: Straight Table Count Distinct - Slowly Changing Dimension in QlikView

Straight Table Count Distinct - Slowly Changing Dimension

win_anthony — Sat, 16 Nov 2024 00:20:21 GMT

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_ID	Date	Month#	Status
1	Jun-20	43983	Reject
1	Jul-20	44013	Approved
2	Jul-20	44013	Reject
3	Jul-20	44013	Reject
4	Jun-20	43983	Reject
4	Jul-20	44013	Approved
5	Jul-20	44013	Reject

Below is the target output

Emp_ID	Date	Month#	Status
1	Jul-20	44013	Approved
2	Jul-20	44013	Reject
3	Jul-20	44013	Reject
4	Jul-20	44013	Approved
5	Jul-20	44013	Reject

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

Kushal_Chawda — Sat, 11 Jul 2020 22:18:32 GMT

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

Anonymous — Sun, 12 Jul 2020 05:38:57 GMT

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

win_anthony — Mon, 13 Jul 2020 15:18:51 GMT

@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_ID	Date	Month#	Status
1	Jun-20	43983	Reject
1	Jul-20	44013	Approved
2	Jul-20	44013	Reject
3	Jul-20	44013	Reject
4	Jun-20	43983	Reject
4	Jul-20	44013	Approved
5	Jul-20	44013	Reject

Re: Straight Table Count Distinct - Slowly Changing Dimension

Kushal_Chawda — Mon, 13 Jul 2020 15:43:47 GMT

How month is calculated?

Re: Straight Table Count Distinct - Slowly Changing Dimension

win_anthony — Mon, 13 Jul 2020 16:10:25 GMT

@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

Re: Straight Table Count Distinct - Slowly Changing Dimension

Kushal_Chawda — Mon, 13 Jul 2020 16:21:38 GMT

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

win_anthony — Mon, 13 Jul 2020 18:33:59 GMT

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

win_anthony — Tue, 14 Jul 2020 20:59:58 GMT

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

Re: Straight Table Count Distinct - Slowly Changing Dimension

Kushal_Chawda — Tue, 14 Jul 2020 21:11:20 GMT

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