Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

Anonymous
Not applicable
Author

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
Author

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 - Specialist
Partner - Specialist

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