topic Re: How to link inline tables with date column of a table in QlikView

How to link inline tables with date column of a table

Anonymous — Tue, 14 Jan 2014 12:36:08 GMT

Hi ,

I have two inline tables that is as follows:

Month:

LOAD * INLINE [

Month, MonthName

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

Year:

LOAD * INLINE [

Year

2011

2012

2013

2014

];

I want to link both the inline tables with a table that has date as one of the columns.

The format of date is "MM/DD/YEAR".

I would appreciate if you help me to link three of them.

Thanks

Geeta

Re: How to link inline tables with date column of a table

Colin-Albert — Tue, 14 Jan 2014 12:39:59 GMT

There is an excellent document on how date fields work within QlikView here that will show you how to format your date fields consistently so they link.

http://community.qlik.com/docs/DOC-3102

Re: How to link inline tables with date column of a table

datanibbler — Tue, 14 Jan 2014 12:40:37 GMT

Hi Geeta,

I don't quite understand the issue in the light of those tables you have posted:

=> If you have a ready-made date_field in your data_table, why bother with those?

<=> Rather, just use the month([date] (num(month([date]))) and year[date] functions to get those additional pieces...

Re: How to link inline tables with date column of a table

Anonymous — Tue, 14 Jan 2014 12:47:07 GMT

try this:

Month:

LOAD * INLINE [

Month, MonthName, key

1, Jan, a

2, Feb, a

3, Mar, a

4, Apr, a

5, May, a

6, Jun, a

7, Jul, a

8, Aug, a

9, Sep, a

10, Oct, a

11, Nov, a

12, Dec, a

];

LEFT JOIN ('Month')

Year:

LOAD * INLINE [

Year, key

2011, a

2012, a

2013, a

2014, a

];

Re: How to link inline tables with date column of a table

Colin-Albert — Tue, 14 Jan 2014 12:49:51 GMT

As DataNibbler says, I'm not sure this is necessary - the date parts can all be derived directly from the one table that has the date fields, no need for the other tables,

Re: How to link inline tables with date column of a table

Tue, 14 Jan 2014 12:50:44 GMT

Try this..

LOAD * INLINE [

Month, MonthName

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

Year:

LOAD * INLINE [

Year

2011

2012

2013

2014

];

Date:
LOAD * INLINE [

    Date, Year,Month
    1-Jan-2013,2013,1
    5-May-2014,2014,5

];

Selva

Re: How to link inline tables with date column of a table

brijesh1991 — Tue, 14 Jan 2014 12:59:48 GMT

Hi,

Why don't you use a standard calender and link your table with this stadanrd calender with date.

Check the attached App. Also see the table view(Ctrl+T)

As far as I understand your problem, it will solve it

-BRIJESH

Re: How to link inline tables with date column of a table

kiranmanoharrode — Tue, 14 Jan 2014 13:02:05 GMT

Dear Geeta,

Use below script,

Month:

LOAD * INLINE [

Month, MonthName

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, Oct

11, Nov

12, Dec

];

Year:

Join(Month)

LOAD * INLINE [

Year

2011

2012

2013

2014

];

Stage2:

Load *,

Date(MakeDate(Year,Month),'MM/DD/YYYY') as CommonDate

Resident Month;

Regards,

Kiran Rode

+91 8976977897