How to use Calendar to filter two tables from Oracle at Qlik Sense Desktop

[Anonymous]

I am using Qlik Sense Desktop. I will load two tables from Oracle. The tables contain Time column and other columns, Table1(time1, value1), Table2(time2, value2). Two tables do not have common key. I will use Table1 to prepare Figure1 and Table2 to prepare Figure2, and put Figure1 and Figure2 on the same Qlik Sense page.

How to use the same Calendar to select date range/filter data range for both Figure1 and Figure2?

Thanks!

[Gysbert_Wassenaar]

Put the data from both Oracle tables into one table:

MyData:

SELECT time1 as time, value1, 'Table1' as SourceTable FROM Table1;

CONCATENATE (MyData)

SELECT time2 as time, value2, 'Table2' as SourceTable FROM Table2;

This way you get one table with one time field that you can use as chart dimension and for your calendar to make selections in.

[Anonymous]

Thanks for your help!

Does CONCATENATE work like UNION Operator?

The two tables are like:

Table1(time1, value1, value11), Table2(time2, value2, value22).

Let me clarify my question,  value1 and value2, value11 and value22 do not have same data types.

My two more problems is:

1: How to set up a default calendar date?

2: I will use another filter from Table1 column at the same time, but this filter should work independently with the above Calendar date range selection?

[Gysbert_Wassenaar]

Does CONCATENATE work like UNION Operator?

Basically yes, but with less restrictions then in sql. Simply specify that you want to concatenate a table to another table and that will happen. It does not matter that fields exist in one table and not the other. The result table will have all the fields.

1: How to set up a default calendar date?

See this blog post: Qlik Sense – Date & Time

2: I will use another filter from Table1 column at the same time, but this filter should work independently with the above Calendar date range selection?

I don't understand how that should work. How are you going to filter and not filter a table at the same time?

[Anonymous]

Dear Gysbert,

Thanks so much for your help!

I may still need to clarify my data source structure, expected result and problem.

My data source is two Oracle tables:

Table1(time1, value1, value11)

Table2(time2, value2, value22, value23, value24, value25, value26).

Column value1 contains different car types; value11 contains sales number

value1 and value2, value11 and value22 do not have same data types.

I would like to get the following result:

1:

Create bar figure1 with column time1, COUNT of car types in column value1 in time series

2:

Create plot figure2 with column time1, accumulation of all car types sale number in column value11 in time series

3:

Create summry_table_3 with column time2, basis statistic of value in column value26 at Table2

Expectation is to:

1:

Use only one calendar to pick up date range, the data range can apply to figure1, figure2 and summry_table_3

2:

Use a filter (or other method) to pick up car types for figure1, this filter (or other method) will ONLY apply to figure1

[Gysbert_Wassenaar]

value1 and value2, value11 and value22 do not have same data types.

Qlik Sense only knows two data types: strings and numbers. Besides that value1, value2, value11 and value22 are all different fields it does not matter if these fields contain different kinds of values.

I would like to get the following result:

And what's stopping you? Have you tried to create the charts you want?

[Anonymous]

Thanks so much for your help!

Yes. I have created charts.

The problem that stops me is:

1: I find a calendar from Qlik branch ( Qlik Branch ) and I use the time1 in Table1 as a dimension to put into the calendar. This calendar will apply to all the charts associates with time1 (figure1 and figure2), and this is good.

   I use Filter pane from Qlik Sense and add value1 in Table1 as dimension. When I use this filter, it will also apply to both figure1 and figure2, but I would like to set it ONLY apply to figure2.

2: data source of my summry_table_3 is from Table2(time2, value2, value22, value23, value24, value25, value26). My above calendar (time1 as dimension) does NOT apply to my summry_table_3. I have not create one more calendar, but I should only use one calendar for all the charts/table on the same Qlik Sense page.

Any suggestion? Thanks!

[Gysbert_Wassenaar]

My above calendar (time1 as dimension) does NOT apply to my summry_table_3

See my first post in this discussion.

[Anonymous]

Thanks so much for your help!

I create new NULL columns and use CONCATENATE to put two Oracle tables together, and then I can use one calendar to pick up the date range for all charts that associate the new concatenated table.

My other big problem is: I need another filter (or other method), and this filter should not be influenced by the date range picker above. However, if I use another column from the table, the data range picker and the new filter will influence each other. Could you please give any suggestion?

[Gysbert_Wassenaar]

You can use set analysis to create expressions that ignore selections in some or all fields. If you have an expression like sum(Amount) you can change it to sum({1}Amount) so that it will ignore all selections. If you want it to ignore all selections except those in the field time1 then you can use an expression like sum({1<time1::$time1>}Amount). If you want to know more about set analysis see this blog post A Primer on Set Analysis