topic Re: loading values from different tables in QlikView

loading values from different tables

Mon, 04 Mar 2013 14:16:41 GMT

Hi Everyone-

I am trying to create an expression in a chart that chooses the most recent date from a series of 3 date fields (Date1, Date2, Date3).

Date1 and Date2 are in table1, and Date3 is in table 2. I've been trying to create a new table that contains the three dates, but have been unsuccessful thus far.

Can anyone advise as to the best way to go about getting the expression to work?

Thanks!

Re: loading values from different tables

manideep78 — Mon, 04 Mar 2013 14:35:55 GMT

Hi,

If your tables contain any key then by using left join you can create a new table which contains three date fields. Below is the example.

Now you can write an expression to get latest dates from three date fields. Let me know if this helps you or not. Its better if you provide sample data to know your requirement and explain what expression you need

Tab1:
LOAD * INLINE [
    ID, Date1, Date2
    1, 02/03/2013, 02/03/2013
    2, 03/03/2013, 03/03/2013
    3, 04/03/2013, 04/03/2013
];
Tab2:
Left Join(Tab1)
LOAD * INLINE [
    ID, Date3
    1, 02/03/2013
    2, 03/03/2013
    3, 04/03/2013
];

Re: loading values from different tables

Anonymous — Mon, 04 Mar 2013 14:55:27 GMT

Hi,

If you just want a table that contains the three dates, you can simply do:

DATES:

LOAD

Date1,

Date1 as Date

RESIDENT

THE_TABLE_WHERE_YOU_GET_DATE1&2_FROM

;

CONCATENATE

(DATES)

LOAD

Date2,

Date2 as Date

RESIDENT

THE_TABLE_WHERE_YOU_GET_DATE1&2_FROM

;

CONCATENATE

(DATES)

LOAD

Date3,

Date3 as Date

RESIDENT

THE_TABLE_WHERE_YOU_GET_DATE3_FROM

;

You will then have all you dates in the same table.

But I think you then want to make some analyses with these dates, so you'd better create a table CALENDAR from the dates.

It all depends on your model and on what you want to do, so explain more about your needs if you need more help.

Re: loading values from different tables

eddysanchez — Mon, 04 Mar 2013 14:56:27 GMT

Hi,

For join this information in one table you need to drop one of them

For example if your first table is named 'Table1' and the other is 'Table2'

with:

*********************************

left join (Table1)

load * Resident Table2;

drop table Table2;

*********************************

you gained join in Table1 the three dates and Table2 is dropped

Re: loading values from different tables

Tue, 05 Mar 2013 14:24:04 GMT

This suggestion helped to create the other table. Now, the problem is that the tables are creating a loop when i load all of the data, as well as the synthetic key for Date1 and Date2.

Re: loading values from different tables

Anonymous — Tue, 05 Mar 2013 14:28:21 GMT

Can you show your qvd or your model ?

Re: loading values from different tables

Tue, 05 Mar 2013 15:00:38 GMT

I had created another table with the calendar that had the same name. once i switched the name of the calendar, it worked.

Thanks for the suggestion