Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
geetaalhan
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
kiranmanoharrode
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
colin_albert

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

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

eruditio
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

];

colin_albert

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

brijesh1991
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

kiranmanoharrode
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