Hello fellow QlikDevs!
I created a report built (partially) upon a Quickbase table. For those of you who've never worked with Quickbase, it's the ultimate User Defined Cloud Hosted database that laughs in the face of Boyce-Codd. I had to do a bit of wrangling to create a normalized data model. So, now I have a table with a record linked to another table that contains "Workgroups" linked by RecordID. There are between 1 and 5 Workgroups for each record, ranking has meaning so the final output looks like this:
Where each unique row contains the key field "Record ID#", 1>=Workgroup<=5, Order (defining rank within the record). The primary "DefConRecord" table contains a field titled "IDP". For each IDP, there are 1-5 Workgroups in Qlikview Tutorial. My task is to match each Workgroup to it's associated employees in another dataset.
I need a way to link each unique Workgroup (a subset of the IDP) to the employees associated with it. I believe an Array, populated with 1-5 Workgroups associated with the unique IDP would give me the tools necessary to iterate through the Employee dataset and find matches.
So, at this point, my data looks like this:
The other half of the dataset is the Employee "Activation" records. A recordset containing the date/time an employee was "Activated" for a given Workgroup. The idea is to track how quickly an employee is activated within a window of time around the DefConRecord Create DateTime. This need to be determined dynamically, as the Record ID# is selected.
My goal is to build either a Line Chart or Scatter chart with the DefConRecord Create Date timestamp as one datapoint and all employees activation timestamps as contrasting datapoint across a window of time.
If I could build a dynamic array out of the Workgroups associated by the IDP, I could match each Workgroup and time window against all Activation timestamps for the matching Workgroups.
I created a variable titled vWorkgroups in the Variable Overview dialog, now I'm trying to work out the best method of populating it with the Workgroups associated with the IDP identified in the DefConRecord within an Expression. Then I can take that variable in a Chart Expression and iterate over Workgroup Activations where WorkgroupArray[x]=ActivationWorkgroup and within Time Window.
Rather that listing my failed attempt's, I'd like to leave the slate clean for your ideas.
Thanks for reading and much gratitude for any suggestions.