topic Merge two date dimensions into one in QlikView

Merge two date dimensions into one

Mon, 02 Jan 2017 08:54:26 GMT

Hello everyone ! Happy New Year to all !!

I have two tables in my report (see snapshot and app attached). One of the table ('Failures') has date as dimension from a data table and another table ('Order') has date as dimension whose data comes from another data table. This date is selected for every row by a condition (refer 'Orders' table expression)

Now, I want to merge these two tables into single table , but the logic used to dates selected in the 'Orders' table should also be included.

Many thanks,

James

Re: Merge two date dimensions into one

HirisH_V7 — Mon, 02 Jan 2017 09:54:51 GMT

Hi ,

Checked your app,

It consists of Different dates but no Calendar connected to them, (

Drop Field and Drop Table

Concatenate vs Link Table

)

Better make a link table for date field and make use of it, If you want Both dates in differently means.

Check this- Canonical Date.

-Hirish

Re: Merge two date dimensions into one

MK_QSL — Mon, 02 Jan 2017 10:02:48 GMT

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Merge two date dimensions into one

Anil_Babu_Samineni — Mon, 02 Jan 2017 10:06:42 GMT

Then, This case just Create one straight table with YearMonth as a dimension and expresions would be Failures and Orders

Or else check the Manish Reply

Re: Merge two date dimensions into one

Mon, 02 Jan 2017 10:08:07 GMT

Hi,

I can relate the Data Island for date with my actual date fields in the expressions which is not a problem. One thing which I wanted to solve is to merge the dates from two data tables out of which one date has a condition before it is merged.

Re: Merge two date dimensions into one

HirisH_V7 — Mon, 02 Jan 2017 10:17:43 GMT

Please make a sample app as suggested by manish and elaborate your'e requirement in a short note.It will be helpful to address.

Re: Merge two date dimensions into one

Mon, 02 Jan 2017 10:37:12 GMT

Ok, Here we go !

1) There is a date 'NYearMonth' comes from a table NCF which is used as dimension in the 'Failures' table in the dashboard.

2) There are two date fields 'GRPostingYearMonth' and 'ABConfLatestYearMonth' comes from EKBE and EKES tables respectively. These dates are consolidated by a condition and displayed as dimension in the 'Orders' table in the dashboard.

The condition used was: =If(BaseForCOTCalc='GR',Aggr(DISTINCT(GRPostingYear&'/'&GRPostingMonth),GRPostingYearMonth),If(BaseForCOTCalc='LA',Aggr(DISTINCT(ABConfLatestYear&'/'&ABConfLatestMonth),ABConfLatestYearMonth)))

Which means, if the flag field 'BaseForCOTCalc' says 'GR' we have to pick up GRPostingYearMonth and if it says 'LA' we have to pick up 'ABConfLatestYearMonth' and display it as dimension. So basically displaying this in a separate table is pretty simple (which I did in the sample app), but merging this date with a date from another table is bit difficult in my case.

Please help me to improve my question if it is unclear.