Expand all | Collapse all

Populate an Array in an Expression

  • 1.  Populate an Array in an Expression

    Posted 02-24-2018 12:11

    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:

    2jpghttps://community.qlik.com/servlet/JiveServlet/downloadImage/2-1196738-150135/2.jpg" width="871">


    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.

  • 2.  RE: Populate an Array in an Expression

    Posted 02-26-2018 10:38
    The only thing I am really getting out of your description is that you have two tables DefConRecord and DefConWG where DefConRecord as the parent table may have at most five child records in DefConWG.
    Presumably the order of the child records is significant and may represent some type of ranking or priority of the child records.

    All the other information including (1) the fields you may have, (2) other tables or relationships you have,  and (3) the reference to Qlikview (some type of Business Intelligence software) is just confusing. Please clarify your question.

    The references to "Populate an Array" (JavaScript!), "Boyce-Codd" (10th normal form), and "DefCon" (the hacker conference) all sound very interesting but they don't help me understand your question.

    Dual Core / All The Things