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: 
zagzebski
Creator
Creator

Scripting: Left join with criteria

I need to left join to a table and only show the records from the Product table that meet the criteria:

Active_Date >= Effective_Date and Active_Date<Term_Date

I am struggling with the getting the proper code/syntax

Example code withouth the above crtieria:

Table_1:

Load

Product ,

Active_Date

Resident Table;

Left Join (Table_1)

Load

Product,

Type_Flag,

Effective_Date,

Term_Date

Resident Product;

1 Solution

Accepted Solutions
Anonymous
Not applicable

for date field

ex : 20101031 use this expression

makedate(left(20101031,4),mid(20101031,5,2),right(20101031,2))

for ex: 40369 use date(40369)

HOPE IT HELPS

View solution in original post

5 Replies
Gysbert_Wassenaar

You'll have to do the join first and then filter the records with a where clause.

Table_1:

Load

Product ,

Active_Date

Resident Table;

Left Join (Table_1)

Load

Product,

Type_Flag,

Effective_Date,

Term_Date

Resident Product;

Result:

noconcatenate

load * resident Table_1

where Active_Date >= Effective_Date and Active_Date<Term_Date;

drop table Table_1;

Make sure the date fields are all real dates and not strings.

You could also try to use the intervalmatch function. See this blog post: IntervalMatch


talk is cheap, supply exceeds demand
zagzebski
Creator
Creator
Author

Thanks!

The date fields are screwing me up

Effective data and term date are in this format (ex 20101031)  while the service date is a number (ex. 40369)

I always struggle knowing the formats and getting them into the same format for evaluation purposes. Any expertise you can provide?

Anonymous
Not applicable

for date field

ex : 20101031 use this expression

makedate(left(20101031,4),mid(20101031,5,2),right(20101031,2))

for ex: 40369 use date(40369)

HOPE IT HELPS

zagzebski
Creator
Creator
Author

Yes this did help -

Is it best in situations like this to get it back to a date field or try to convert to a number if there is a need to compare these fields?

Anonymous
Not applicable

it really depend on the script.........

i usally  change everything into number format like '40369' and in the front end at chart

i use date in number tab