Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

How to link inline tables with date column of a table

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

1 Solution

Accepted Solutions
Creator III
Creator III

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

View solution in original post

7 Replies

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

Champion
Champion

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...

Partner
Partner

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

];

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,

Not applicable

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

Partner
Partner

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

Creator III
Creator III

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

View solution in original post