topic Re: Mapping a Date field with multiple values in QlikView

Mapping a Date field with multiple values

divya_anand — Wed, 11 Jan 2017 11:27:16 GMT

Hi,

This is the current scenario in my datamodel.

Bridge Table is the product of mapping Date2 & Date3 into 1 field %Date.

How do I also include Date1 into the field %Date in the BridgeTable with Type=1?

I understand Mapping load similar to what was done for Date2 & Date3 should work.

However, Every ID in Table 2 has single value for Date2 & Date3 which is not the case in Table1.

Date1 has multiple values for each ID in Table1.

Any Suggestions on this?

Attached below is a sample qvw.

Kindly let me know if more details are needed in understanding this.

Thank you.

Re: Mapping a Date field with multiple values

enriquem — Wed, 11 Jan 2017 11:32:35 GMT

Hi,

To include Date1 into %Date you must load your data from Date1 and concatenate to the table BridgeTable and put here the Dates registers.

Concatenate

BridgeTable:

Load Date1 as %Date

resident your_Table;// from your_Table;

Remember, that it's important do it inmediatly that you build your BridgeTable, or the concantenate wouldn't be necesary.

Regards

Re: Mapping a Date field with multiple values

divya_anand — Wed, 11 Jan 2017 12:00:06 GMT

Hi Enrique,

Thank you for the response.

If I concatenate like you've mentioned, %Date is having all possible values. But how do I come up with an output as shown in picture2?

Re: Mapping a Date field with multiple values

enriquem — Wed, 11 Jan 2017 12:34:42 GMT

Hi,

Sorry, You must concatenate the fields considering the ID and Date1, so when you do de load you must add:

Concatenate

BridgeTable:

Load

Distinct Autonumber(Date1) as ID, // or whatever_you_do_to_get_the_ID as ID

Date1 as %Date

resident your_Table;// from your_Table;

Regars.

Re: Mapping a Date field with multiple values

enriquem — Wed, 11 Jan 2017 12:38:59 GMT

Hope that the last post help you,

I forgot tell you that with the DISTINCT keyword you will put all the Date1 that don't have your BridgeTable, so pay attention that you don't have duplicated values, and the result of Dates will be the junction of the Date1 and Date2.

Regards

Re: Mapping a Date field with multiple values

divya_anand — Wed, 11 Jan 2017 12:40:44 GMT

Hi Enrique,

I really appreciate your time & efforts.

Do you mean I should use Autonumber ?

Would be really nice if you could use the qvw that I've attached and show how to do this.

Thank you.

Re: Mapping a Date field with multiple values

enriquem — Wed, 11 Jan 2017 13:00:23 GMT

Hi,

You must use your previous ID, if you were using the AUTONUMBER function you must use it again. In other hand if you are using an ID field you must use this ID fiel again, so you must use whatever you were using.

I tell you about Autonumber function because it's more efficient for the fields that relation the tables for your future querys, but you can use an id or the date directly.

I will download your qvw and try to give you a solutions

Regards.

Re: Mapping a Date field with multiple values

enriquem — Wed, 11 Jan 2017 14:55:24 GMT

Hi,

First, I must understand your model, so what means the ID of the Table1 and Table2 and its dates? Because if the ID don't have relationship to the Date it's one important point.

I will put my example about how i will resolve your problem, in this model you have a bridge table that connect the table1 and table2 (I suppose that there are the fact table) and i create a calendar between their dates. Now if you apply a filter in the fields on Calendar dimension you will see the corresponding records from Table1 and Table2.

Maybe this isn't an easy solutions, but it's work and it's an easy way to understand without Maps.

Regards