topic Scripting: Left join with criteria in QlikView

Scripting: Left join with criteria

zagzebski — Mon, 24 Jun 2013 17:12:46 GMT

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;

Re: Scripting: Left join with criteria

Gysbert_Wassenaar — Mon, 24 Jun 2013 17:22:20 GMT

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

Re: Scripting: Left join with criteria

zagzebski — Mon, 24 Jun 2013 17:52:54 GMT

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?

Re: Scripting: Left join with criteria

Anonymous — Mon, 24 Jun 2013 19:00:38 GMT

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

Re: Scripting: Left join with criteria

zagzebski — Mon, 24 Jun 2013 19:21:15 GMT

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?

Re: Scripting: Left join with criteria

Anonymous — Mon, 24 Jun 2013 19:42:57 GMT

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