Sorry for the wall of text. I am new to QLIK and mainly have SQL background. I was told QLIK is based to some degree on SQL. But there are few things it does that I need clarification on. Unfortunately, I don't have access to their QLIK dashboard yet have a limited data to work with. Below are the filter issues they are running into. Note that they are using this same load for about 12 different bar charts and donuts on one dashboard
Filter behaviors and current issues:
- filter on gains, then losses => Gains filter removed [should remain]
- filter on losses, then Top schools => Losses filter removed [should remain]
- filter on Top Schools, then (common) STRL => filters work together
- filter on Top Schools, then STRL, then GSE => filters work together
- filter on TS, then STRL, then GSE, then Gains.Hiring Authority => all filters (but gains) removed [should remain]
- filter on gains, then STRL => filters work together
- filter on STRL, then losses, then another dimension of losses => filters work together
^ seems common filters work with any 1 set, but any dataset-specific filter does not work with any filter from other datasets
I suggested they only load in one table each instead of loading two copy's each of the the Onboard, Gain, and Loss and then use a JOIN. But they told me that QLIK( see their comment under previously) already does the JOINS for you. Currently it looks like they are concatenating everything so I'm not sure if this overrides the JOIN? Below is the load they are currently trying to do and I'm wondering if they are going about this correctly? I see QLIK has something called set analysis where you can script the filter behaviors. Not sure if this would be a good option here?
//--------------------------------------------------PREVIOUSLY--------------------------------------------
--------------client comments on QLIK----------------
// When you edit the data loading script itself, you do not need to dictate associations.
// Data that shares the same name will be linked together, because that is how SQL (Structured Query Language) operates
// and is the same thing as a Qlik association.
-----START OF THIER LOAD
// Loading 2 copies of each Onboard, Gain, and Loss record: 1 with all common fields, 1 copy with all dataset-specific fields,
// so that global filters work as and where desired.
// effecting 2 separate counts (cols) for each dataset.
// Unqualify *;
// // GAINS-1 with common fields
// [FY22 basetable]:
// LOAD
// [Fiscal Year],
// [To Major Command] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [To UIC] AS [UIC],
// [To UIC Cd] AS [UIC Cd],
// [To Org Code] AS [Org Struct Cd],
// // [To Title] AS [Job Title],
// // [To GSA Loc] AS [Loc GeoLoc],
// // [To Occ Grp] AS [Occ Grp],
// // [To Occ Series] AS [Occ Series],
// // [To Occ Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [To Pay Plan] AS [Pay Plan Cd],
// [Pay GSE],
// [To Grade] AS [Pay Grade],
// [Pay Rank],
// // [NRDE Gain],
// // [STRL Gain],
// // [WC/Lab Gain]
// [NRDE Gain] as [NRDE gc cnt], // gain common-fields count, for NRDE boundary
// [STRL Gain] as [STRL gc cnt], // gain common-fields count, for STRL boundary
// [WC/Lab Gain] as [WC gc cnt] // gain common-fields count, for WC/Lab boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 gains (xfrm) 20231119.txt]
// (txt, codepage is 1252, embedded labels, delimiter is '|', msq)
// WHERE [NRDE Gain] > 0 OR [STRL Gain] > 0 OR [WC/Lab Gain] > 0;
// // LOSSES-1 w/common fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Fr Major Command] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [Fr UIC] AS [UIC],
// [Fr UIC Cd] AS [UIC Cd],
// [Fr Org Code] AS [Org Struct Cd],
// // [To Title] AS [Job Title],
// // [Fr GSA Loc] as [Loc GeoLoc],
// // [Fr Occ Grp] AS [Occ Grp],
// // [Fr Occ Series] AS [Occ Series],
// // [Fr Occ Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Fr Pay Plan] as [Pay Plan Cd],
// [Pay GSE],
// [Fr Grade] AS [Pay Grade],
// [Pay Rank],
// // [NRDE Loss],
// // [STRL Loss],
// // [WC/Lab Loss]
// [NRDE Loss] as [NRDE lc cnt], // loss common-fields count, for NRDE boundary
// [STRL Loss] as [STRL lc cnt], // loss common-fields count, for STRL boundary
// [WC/Lab Loss] as [WC lc cnt] // loss common-fields count, for WC/Lab boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 losses (xfrm) 20231101.txt]
// (txt, codepage is 28591, embedded labels, delimiter is '|', msq)
// WHERE [NRDE Loss] > 0 OR [STRL Loss] > 0 OR [WC/Lab Loss] > 0;
// // ONBOARDS-1 w/common fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Major Command Cd] as [MAJCOM],
// [STRL],
// [WC/Lab],
// // [UIC],
// [UIC Cd],
// [Org Struct Cd],
// // [Job Title],
// // [Loc GeoLoc],
// // [Occ Grp],
// // [Occ Series],
// // [Occ Series Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Pay Plan Cd],
// [Pay GSE],
// [Pay Grade],
// [Pay Rank],
// // [person cnt] AS [onboard]
// [person cnt] AS [oc cnt] // onboard common-fields count, for any boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 Onboards-MQ 20231031c-ml.txt]
// (txt, codepage is 28591, embedded labels, delimiter is '|', msq)
// WHERE [person cnt] > 0;
// // TOP SCHOOLS-1 w/common fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Major Command Cd] as [MAJCOM],
// [STRL],
// [WC/Lab],
// // [UIC],
// [Unit ID Code] AS [UIC Cd],
// [Organization Structure ID] AS [Org Struct Cd],
// // [Title] AS [Job Title],
// // [Location] AS [Loc GeoLoc],
// // [Occ Grp],
// // [Occ Series],
// // [Occ Series Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Pay Plan Cd],
// [Pay GSE],
// [Pay Grade],
// [Pay Rank],
// // [person cnt] AS [O-TS cnt]
// [person cnt] AS [sc cnt] // schools common-fields count, for any boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 Top 50 Schools.csv]
// (txt, codepage is 28591, embedded labels, delimiter is ',', msq)
// WHERE [person cnt] > 0;
// // now add file specific attributes in effect duplicating and extending the records above
// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
// // GAINS-2 w/g-specific fields
// Concatenate([FY22 basetable])
// LOAD
// [Fiscal Year],
// [To Major Command] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [To UIC] AS [UIC],
// [To UIC Cd] AS [UIC Cd],
// [To Org Code] AS [Org Struct Cd],
// // [To Title] AS [Job Title],
// // [To GSA Loc] AS [Loc GeoLoc],
// // [To Occ Grp] AS [Occ Grp],
// // [To Occ Series] AS [Occ Series],
// // [To Occ Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [To Pay Plan] AS [Pay Plan Cd],
// [Pay GSE],
// [To Grade] AS [Pay Grade],
// [Pay Rank],
// // [Request Number] AS [Action: ReqestNo],
// [Action: Type] AS [Gain Type],
// [Action: NOA] AS [Gain NOA],
// [NOA Cd] AS [Gain NOAC],
// // [NOA] AS [g NOA],
// [Hiring Authority Cited],
// [Hiring Authority Cited Dtl],
// [Where From],
// [Where From Dtl],
// //[Where From NV],
// // [NRDE Gain],
// // [STRL Gain],
// // [WC/Lab Gain]
// [NRDE Gain] as [NRDE gs cnt], // gain-specific fields count, for NRDE boundary
// [STRL Gain] as [STRL gs cnt], // gain-specific fields count, for STRL boundary
// [WC/Lab Gain] as [WC gs cnt] // gain-specific fields count, for WC/Lab boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 gains (xfrm) 20231119.txt]
// (txt, codepage is 1252, embedded labels, delimiter is '|', msq)
// WHERE [NRDE Gain] > 0 OR [STRL Gain] > 0 OR [WC/Lab Gain] > 0;
// // LOSSES-2 w/l-specific fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Fr Major Command] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [Fr UIC] AS [UIC],
// [Fr UIC Cd] AS [UIC Cd],
// [Fr Org Code] AS [Org Struct Cd],
// // [To Title] AS [Job Title],
// // [Fr GSA Loc] as [Loc GeoLoc],
// // [Fr Occ Grp] AS [Occ Grp],
// // [Fr Occ Series] AS [Occ Series],
// // [Fr Occ Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Fr Pay Plan] as [Pay Plan Cd],
// [Pay GSE],
// [Fr Grade] AS [Pay Grade],
// [Pay Rank],
// [Action: Type] AS [Loss Type],
// [Action: NOA] AS [Loss NOA],
// [NOA Cd] AS [Loss NOAC],
// [Where To],
// [Where To Dtl],
// // [Where To NV],
// // [NRDE Loss],
// // [STRL Loss],
// // [WC/Lab Loss]
// [NRDE Loss] as [NRDE ls cnt], // loss common-fields count, for NRDE boundary
// [STRL Loss] as [STRL ls cnt], // loss common-fields count, for STRL boundary
// [WC/Lab Loss] as [WC ls cnt] // loss common-fields count, for WC/Lab boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 losses (xfrm) 20231101.txt]
// (txt, codepage is 28591, embedded labels, delimiter is '|', msq)
// WHERE [NRDE Loss] > 0 OR [STRL Loss] > 0 OR [WC/Lab Loss] > 0;
// // ONBOARDS-2 w/o-specific fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Major Command Cd] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [UIC],
// [UIC Cd],
// [Org Struct Cd],
// // [Job Title],
// // [Loc GeoLoc],
// // [Occ Grp],
// // [Occ Series],
// // [Occ Series Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Pay Plan Cd],
// [Pay GSE],
// [Pay Grade],
// [Pay Rank],
// [YOS: Years],
// // [person cnt] AS [Onboard cnt]
// [person cnt] AS [os cnt] // onboard-specific fields count, for any boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 Onboards-MQ 20231031c-ml.txt]
// (txt, codepage is 28591, embedded labels, delimiter is '|', msq)
// WHERE [person cnt] > 0;
// // TOP SCHOOLS-2 w/s-specific fields
// Concatenate([FY22 basetable])
// LOAD
// [EffectiveDt: Fiscal Year] AS [Fiscal Year],
// [Major Command Cd] AS [MAJCOM],
// [STRL],
// [WC/Lab],
// // [UIC],
// [Unit ID Code] AS [UIC Cd],
// [Organization Structure ID] AS [Org Struct Cd],
// // [Title] AS [Job Title],
// // [Location] AS [Loc GeoLoc],
// // [Occ Grp],
// // [Occ Series],
// // [Occ Series Cd] AS [Occ Cd],
// [Pay Demo Cd],
// [Pay Plan Cd],
// [Pay GSE],
// [Pay Grade],
// [Pay Rank],
// [EOD Year] AS [O-TS.EOD Year],
// [SchoolOnHire],
// // [person cnt] AS [TopSchool cnt]
// [person cnt] AS [ss cnt] // schools-specific fields count, for any boundary
// FROM [lib://NRDE_Data_Enterprise_Dashboard_Work/FY22 Top 50 Schools.csv]
// (txt, codepage is 28591, embedded labels, delimiter is ',', msq)
// WHERE [person cnt] > 0;
