I am struggling to get the correct qliksense script for the below query:
select count (P_ID), PC_ID , CASE WHEN COUNT ( P_ID) <= 10 THEN 'Junior'
WHEN COUNT ( P_ID) >= 11 THEN 'Big' END COMPANY_SIZE_CATEGORY FROM (SELECT A.P_ID, A.PC_ID, A.CONTRACT_DLVR, A. RESGN_DLVR, TO_NUMBER (TO_CHAR (A.CONTRACT_DLVR, 'yyyymmdd')) abc FROM INS_STS A , INS_MST B WHERE A.P_ID = B.INSURED_ID
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID FROM INS_SAL_CHG ) AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <= '202204' AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE) ,'YYYYMM') >'202204' )) ) ins_sts_tbl WHERE abc = (SELECT MAX (abc) FROM (SELECT TO_NUMBER (TO_CHAR (CONTRACT_DLVR, 'yyyymmdd')) abc FROM INS_STS A WHERE
AND A.CONTRACT_ID IN (SELECT CONTRACT_ID FROM INS_SAL_CHG ) AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <=202204 AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE),'YYYYMM') >202204)) AND P_ID = ins_sts_tbl.P_ID)) GROUP BY PC_ID;
I can use the sql directly but here we have provide the duration for month-year
AND (TO_CHAR (A.CONTRACT_DLVR, 'YYYYMM') <= '202204' AND (TO_CHAR (NVL (A.RESGN_DLVR, SYSDATE) ,'YYYYMM') >'202204'
We have created "Month-Year" field and provided that as filter in the dashboard. Users will only select the values from this and accordingly expecting the results in the dashboard.
in the query, it is hardcoded but if we remove the hardcoded then how can we associate with master calender.
Note: In master calendar we need both of these dates to be used.
not really sure what your problem is. the where clause in your sql and association between two data model tables completely play different roles. the where clause limits the data you are pulling in, association is intended to link your tables.
i dont see how removing the conditions that limit your data have to do with association - to associate two tables, they just need to have one common field.
the table created by the script above does not have a date field so it will not associate with the calendar. it only has the following fields:
count (P_ID), PC_ID , COMPANY_SIZE_CATEGORY
if this is your fact and the count varies with time, then you need to add a time field to it, possibly: A.CONTRACT_DLVR NVL (A.RESGN_DLVR, SYSDATE)
