topic Help with referencing one field in another table in QlikView

Help with referencing one field in another table

Wed, 23 Sep 2009 15:35:29 GMT

Hi, I've got a query about writing what should be a relatively straight forward bit of script.

What I have is two tabs on the go, each with a seperate table in it saved to seperate qvd files. In one table I want to make a comparison of dates like so:

date (field name) - date (field name 2) as [Date difference]

However, this second field name is within the second tab, how do I reference it so that when I run the script it only pulls in the data for that one field and not the entire table again?

Help!

Help with referencing one field in another table

Wed, 23 Sep 2009 18:00:32 GMT

Hi,

I think you will need to attached the Date difference field to the most populated table. You can do a

left join (BigTable) Load CommonKey,
date (field name) - date (field name 2) as [Date difference]
resident SmallerTable;

after the BigTable load, assuming that SmallerTable is already loaded.

Cheers,

Jane

Help with referencing one field in another table

Wed, 23 Sep 2009 18:08:32 GMT

Hi,

thanks for the suggestion, can you possibly illustrate this a little further? If I currently have my set up as:

Tab 1 - With table 1 in

===================================================================================

Tab 2 - Open

Table 2:

Load

Field 1

Date (Field 1) - Date (Field 2) as [Date Difference]

SQL Select *

From Table 2

=============================================================

With Field 2 located in Tab 1, Table 1.

What would the common key be? Something that links the two tables? Would I have to load this along with the field I am interested in?

Cheers

Ben

Help with referencing one field in another table

Wed, 23 Sep 2009 18:20:13 GMT

Hi Ben,

Could you attach script. I assume that you want to calculate the date difference for each line, so there must be some link between the two tables but I cannot visualise your data with the info so far.

Cheers,

Jane

Help with referencing one field in another table

Wed, 23 Sep 2009 19:58:51 GMT

hi, I don't know how to really attach the script let me know if this isnt what you meant:

Left

Join

(AP_detail)

LOAD

RMPYID

as [Payment ID]

RMDCTM

as [Payment Doc]

// RMDOCM as [Doc No],

// RMPYE,

// RMGLBA,

RMDMTJ as [Payment Date]

Date

(MakeDate(1900+left(text(RMDMTJ),len(RMDMTJ)-3))+right(text(RMDMTJ),3)-1) as [Date Of Payment]

Date

(RMDMTJ) - Date (RPDDJ) as [Actual Payment Days];

// RMVDGJ,

// RMICU,

// RMICUT,

// RMDICJ,

// RMPAAP,

// RMCRCD,

// RMCRRM,

// RMAM,

// RMVLDT,

// RMPYIN,

// RMISTP,

// RMCBNK,

// RMBKTR,

// RMTORG,

// RMUSER,

// RMPID,

// RMUPMJ,

// RMUPMT,

// RMJOBN;

SQL

SELECT

FROM

TRDTA.F0413;

Store

AP_detail into data

/AP_detail.qvd;

In this script I need to make the statement understand that RPDDJ is infact in another table called AP_Ledger, in database called TRDTA.F0411 however I need to make sure that only the RPDDJ is loaded from the other table, otherwise the load time will be far too long,

Hope this helps

Ben

Help with referencing one field in another table

Wed, 23 Sep 2009 21:12:59 GMT

Hi,

Can you email the QV doc (with no data loaded) to me at jane.jackman@aero-inventory.com as that does not make sense top me.

Cheers,

Jane