Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amit_shetty78
Creator II
Creator II

Use dataset returned from R as a dimension

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.

Labels (1)
2 Replies
amit_shetty78
Creator II
Creator II
Author

Hi Tobias tln,

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

Thanks.

Anonymous
Not applicable

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)