Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
zagzebski
Contributor

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
srikanthsri1
Valued Contributor

Re: Scripting: Left join with criteria

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

5 Replies

Re: Scripting: Left join with criteria

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
Contributor

Re: Scripting: Left join with criteria

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?

srikanthsri1
Valued Contributor

Re: Scripting: Left join with criteria

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
Contributor

Re: Scripting: Left join with criteria

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?

srikanthsri1
Valued Contributor

Re: Scripting: Left join with criteria

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

Community Browser