topic Re: Date load constraint in QlikView

Date load constraint

Fri, 22 Jan 2016 12:09:44 GMT

I have a header table and a lines table.

The lines table is linked to the header only on one reference number.

The line table has thousands of records and want to date constrain the load to a smaller period than the total content but there are no fields that would allow me to do this as they are pure transactional information with no reference numbers I can use with a greater than style.

The header table has a date field.

If I load the header table first and join to the latter I only correctly give the header info to the the first line of the lines table.

I want to load the lines table first and then join the header but this means loading all the lines table.

Thanks for any help.

Re: Date load constraint

Fri, 22 Jan 2016 15:05:22 GMT

These two tables are 1-1 relation or 1-n or n-n relation ?

Can you please post some sample data how these tables related ?

Re: Date load constraint

Fri, 22 Jan 2016 20:50:30 GMT

The table is Header : Lines 1:n

Header

Ref Date CustomerCode

Lines

Ref ItemCode Qty

Thanks

Re: Date load constraint

dominicmander — Sat, 23 Jan 2016 08:41:42 GMT

Is the reference number that relates the two not a sequential number?

If it is, you could put a condition on that.

Otherwise, if your data source is a SQL database you could easily join the two tables in the database and apply your condition that way.

Re: Date load constraint

Anonymous — Sat, 23 Jan 2016 10:11:01 GMT

You could do it in your database extract sql with something like this

select LineTable.blah

from HeaderTable, LineTable

where HeaderTable.Ref = LineTable.Ref

and HeaderTable.Date > YourDate

Re: Date load constraint

maxgro — Sat, 23 Jan 2016 11:22:54 GMT

Load the header filtering by date in the SQL part

load

SQL select ........... from HeaderTable where HeaderTable.Date > YourDate

Load the lines filtering by date in the SQL part (as Bill suggested, with a sql join between header and lines)

load

SQL select LineTable.blah

from HeaderTable, LineTable

where HeaderTable.Ref = LineTable.Ref

and HeaderTable.Date > YourDate

Re: Date load constraint

swuehl — Sat, 23 Jan 2016 11:30:37 GMT

If you want to use QV script code (e.g. because you are not querying a DBMS):

Header:

LOAD

Ref, Date, CustomerCode

FROM YourHeaderTableSource

WHERE Date > '2015-01-01'; // or whatever threshold you want to use.

Lines:

LOAD

Ref, ItemCode, Qty

FROM YourLinesTableSource

WHERE EXISTS(Ref);