2 Replies Latest reply: Jan 15, 2018 6:16 PM by Tobias Lindulf RSS

    Use dataset returned from R as a dimension

    Amit S

      Hi,

       

      I have an expression that I use for getting a dataset from R which is returning me unique dates and its corresponding values. But both these values are available to me as measures in QS. Is it possible to use the dates returned as a Dimension someway or used to select values in an existing dimension?

       

      I have a date dimension that I generate in the load(All dates between 01-01-2010 - 01-01-2020) which is a superset of all possible dates that can be returned by R but if I use this dimension the values from R a populated against the first n dates, where n is the number of values returned from R.

       

      For e.g., the date value pair in R is:

      Date                    Value

      12-03-2014          12.03

       

      But in the table in QS it would show as:

      Date_From_Load     Value

      01-01-2010               12.03

       

      Unless I manually select 12-03-2014 in my Date_From_Load dimension there is no way the date-value mapping will be correct.

       

      Appreciate any help on this.

       

      Thanks.

        • Re: Use dataset returned from R as a dimension
          Amit S

          Hi Tobias tln,

           

          Would you any thoughts on the best way to deal with this issue?

           

          Thanks.

          • Re: Use dataset returned from R as a dimension
            Tobias Lindulf

            There are some base rules when using SSE from analysis mode when using tensor functions (not aggregating functions):

            1. The cardinality (number of rows) must be preserved, meaning if Qlik is sending out 3 rows of data (i.e 3 Dates) then Qlik is expecting 3 rows of values back in the response from SSE (or R in this case). If Qlik is sending out 7 rows of data (i.e 3 Dates) then Qlik is expecting 7 rows of values back in the response. This is based on what selections that are made in Qlik.

            2. The order (sort order) must be preserved, meaning if Qlik is sending out 2 rows of data (i.e 2017, 2006) then Qlik is expecting 2 rows of values back in the response from SSE (or R in this case) and the values in the same order (i.e. Value(2017), Value(2006)). If Qlik is sending out 3 rows of data (i.e 2001, 2003, 2000) then Qlik is expecting 3 rows of values back in the response from SSE and the values in the same order (i.e. Value(2001), Value(2003), Value(2000)).

            3. The SSE side never knows in which order the data from Qlik comes in, but usually it is the order of how it was loaded into Qlik (in the data reload).

             

             

            One way of doing this in a script in R is the following.

            Lets say that the data.frame that you always have in R (the big table with all dates and all Values) is named allData with two columns "Date" and "Value".

            Qlik is sending rows of Date, based on the selections and the dimension is Date_From_Load (that you have loaded into Qlik in the data reload step) and those are always populated in the q data.frame.

             

            In analysis mode (in any chart) you want a measure populated from R based on the dates selected in your app.

            you simply use the following expression:

            R.ScriptEvalEx('S', 'q$ReturnValues <- with(allData, Value[match(q$Date_From_Load, Date)]); q$ReturnValues;', Date_From_Load)