Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I need to show the latest record in the filter pane. The firstsortedvalue is working as expected but only when I created a measure. Is there a way to show the latest record only in the filter pane.
For example: I only want to show NLS-FSMFTB in the filter pane.
NLS |
NLS- FMFTB |
@has35526 That is the goal, right? IF just one you want to show always, can be static like this.
If(Match(company_name, 'NLS- FMFTB'), company_name)
Below query in the database is giving us the correct result
SELECT fcci.crid, fcci.company_name, fap.master_mid, fap.acc_num AS master_epa, fap.acc_num, fap.prod_wrkflw_name, fap.prod_grp_name, DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) AS processing_date
FROM fcci
INNER JOIN (select crid, mid, acc_num, wrkflw_name, grp_name, DATE_TRUNC('month', start_date) as start_date, LAST_DAY (end_date) as end_date
from (
select *, row_number() OVER (PARTITION BY crid, wrkflw_name, LAST_DAY (start_date) ORDER BY start_date DESC) AS row_number
from (select * from facts_profile where master_mid = mid)
)
WHERE row_number = 1) fap ON fcci.crid = fap.crid
AND DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) BETWEEN fap.start_date AND NVL(fap.end_date, SYSDATE);
Why Dont you create a Flag or another Field in Script only and than use that as a filter like:-
Load Company_name as C.Name,crid&master_mid&-------- as key1;
SQL
SELECT fcci.crid, fcci.company_name, fap.master_mid, fap.acc_num AS master_epa, fap.acc_num, fap.prod_wrkflw_name, fap.prod_grp_name, DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) AS processing_date
FROM fcci
INNER JOIN (select crid, mid, acc_num, wrkflw_name, grp_name, DATE_TRUNC('month', start_date) as start_date, LAST_DAY (end_date) as end_date
from (
select *, row_number() OVER (PARTITION BY crid, wrkflw_name, LAST_DAY (start_date) ORDER BY start_date DESC) AS row_number
from (select * from facts_profile where master_mid = mid)
)
WHERE row_number = 1) fap ON fcci.crid = fap.crid
AND DATE_TRUNC('month', DATEADD(MONTH, '0', CURRENT_DATE)) BETWEEN fap.start_date AND NVL(fap.end_date, SYSDATE);
now this key1 will link to you table and use C.Name in Filter pane or
use create a calulated Dimension Using this C.name and aggreate it on Company_Name;
Because this is a quite a good query and creating set analysis of this will require a dataset of all those columns with atleast 1000 rows
and still then there will be problems while using Possible Function,
as it will require atleast 2 possible functions according me and then which filters should affect or not affect possible functions are another case.
If this is already capturing directly writing SQL. Then you can load with this query into Qlik and see how the result looks like?
If due to join limitation, I would rather think to store all the SQL query into Views and call only view into Qlik.