Filter data from one table to populate chart with data from another table

[bruce_sorge]

Hello,

I have an Oracle database that I extracted data from and now I need to create a point map chart.

I can do this in my expression:

=if(PROJ_END_DT>= AddYears(today(),-5), PROJ_END_DT) to get the date five years ago. What I want to do is take the result of that, and get zip codes from another table that fall within that date range to populate the map. These are both in different tables.

Proj_End_Dt is in a table called Abstracts, and the Zip is in another table called Locations, and there is a relationship set up in QlikSense. I tried adding =Zip under and above the other expression to no avail. I get a message:

=if(PROJ_END_DT>= AddYears(today(),-5), PROJ_END_DT) -Zip - Point Layer

The data contains invalid geometries that could not be shown on the map. Review your data for errors and try again.

In the expression editor, there are no errors.

Thanks

[JordyWegman]

Hi Bruce,

Maybe map the project end date to the locations with the zip? Maybe then you make this formula (maybe now its also possible):

=if(PROJ_END_DT>= AddYears(today(),-5), Zip)

Jordy

Climber

[bruce_sorge]

Hi Jordy,

Thanks for the response. It pointed me in the right direction. So I updated my expression to this:

=If(PROJ_END_DT,'DD-MM-YY')>=AddYears(Today()-5,ZIP_CODE)

I don't receive any errors on in the expression editor, but in the map I get:

qlik the data contains invalid geometries that could not be shown on the map. So apparently it's not pulling the zip codes, am I right? Do I need to preload or something?

Thanks

Bruce

[JordyWegman]

Hi Bruce,

If this is the exact formula, there are some syntax errors, although Qlik isn't giving any..

=If((PROJ_END_DT,'DD-MM-YY')>=AddYears(Today()-5),ZIP_CODE)

You were missing a '(' in the beginning for the Project end date and a ')' behind the 5 for closing the AddYears.

Jordy

Climber 

[bruce_sorge]

Hmm, it's not working. I get an error in expression ')' expected. Funny thing is that all the parens are there.

[JordyWegman · Accepted solution]

Hi Bruce,

I see that I have made some mistakes..

=If(Date(PROJ_END_DT,'DD-MM-YY')>=AddYears(Today()-5),ZIP_CODE)

There was no Date function  for the formatting.

Jordy

Climber

[bruce_sorge]

Thank you sir. That is exactly what I needed. As an aside, I am attending the conference in Dallas next week and hopefully I will learn a lot to help me with my projects.

[JordyWegman · Accepted solution]

No problem, good luck there!

Jordy

Climber