topic Help Connecting Date Fields in QlikView

Help Connecting Date Fields

DSTaylor — Thu, 12 Mar 2020 16:17:43 GMT

Hi,

I'm hoping one of the Qlik geniuses from the community can help me.

I've got 2 tables that already join on one field: Code

Table 1 has Code, Costs & CostDate

Table 2 has Code, Production Line Milestones & LineDate

I currently have a chart set-up that's showing information from table 1 so:

Date as dimension on X-Axis

Sum of Costs (cumulative) over time

This is displayed only when GetSeletedCount(Code)=1 so there is not lots of data on the chart.

I now want to plot single points on the chart using the Milestone information from Table 2 to show the Cumulative Cost & Milestone information together.

The issue is it is already join on Code so I can't just rename Date2 as Date1 as I'll get a Synthetic Key.

I hope this is making sense so far.

I have no idea how to proceed or if it's possible.

Is anyone able to help?

Re: Help Connecting Date Fields

Arthur_Fong — Wed, 18 Mar 2020 07:42:45 GMT

You have a sample app?

Re: Help Connecting Date Fields

jonathandienst — Wed, 18 Mar 2020 10:55:25 GMT

I would consider merging the two tables, something along he lines of:

Data: LOAD Code, Cost, CostDate as Date, 'Cost' as Type, .... FROM ...; Concatenate(Data) LOAD Code, [Production Line Milestones] as MileStone, LineDate as Date, 'MileStone' as Type, ... FROM ...;

Now you can plot them with the same dimension, using Type in set expression to select the correct data:

=Sum({<Type = {'Cost'}>} Cost)
=Count({<Type = {'MileStone'}>} MileStone)

Your question is a little light on details, so you will probably need to tweak this to suit.