Where condition failing

[imrasyed]

Hi Experts,

I have two tables.Joined them and created a third table using Resident for getting the data based on the condition of two fields coming from two  tables.

TableA:

X,Y

TableB:

X,Z

TableC:

X,Y,Z

resident TableA

where Y <=Z;

so this where condition is not working?

Where am I going wrong?

Appreciate u r help.

[sunny_talwar]

Join TableA and TableB so that you can perform the condition in where statement

TableA:

X,Y

Join (TableA)

X,Z

TableC:

X,Y,Z

resident TableA

where Y <=Z;

[imrasyed]

Yes Sunny I tried that as well but still its the same issue.

In Y column I have year from 2012 to 2019 and in Z I have  Year 2018.

So acccording to the condition it should fetch 2012 to 2018  instead it is fetching only  for Yr2018 

[jolivares]

So well I don't understand your problem... could you post a sample table to see what happen to you, cause I agree with Sunny.  This is a simple case to solve with a Join

[imrasyed]

Hi Juan,

Here is the sample data.

Temp:

Load * Inline

[

    id, Customer, Sales,Clyr,Clyr1

     1,A, 100,2012,2012

     2,B, 200,2013,2013

     3,A, 300,2013,2013

     4,C, 150,2014,2014

     5,D, 200,2015,2015

     6,B, 140,2016,2016

     7,E, 400,2017,2017

     8,g,200,2013,2013

     9,C, 340,2018,2018

     10,F,600,2019,2019

];

outer Join(Temp)

Final:

Load * Inline

[

    Clyr,MCl,MClyr

    2018,2018,03

     ];

NoConcatenate

ts:

load *

Resident Temp

where Clyr1 <= MCl;

drop Table Temp;

Here I am getting only yr 2018 data.Instead I want to get  2012 to 2018.

Please can you suggest me where i am going wrong.

[sunny_talwar · Accepted solution]

Try this - Basically don't join on C1yr... just do a Cartesian join

Temp:

LOAD * INLINE [

    id, Customer, Sales, Clyr, Clyr1

    1, A, 100, 2012, 2012

    2, B, 200, 2013, 2013

    3, A, 300, 2013, 2013

    4, C, 150, 2014, 2014

    5, D, 200, 2015, 2015

    6, B, 140, 2016, 2016

    7, E, 400, 2017, 2017

    8, g, 200, 2013, 2013

    9, C, 340, 2018, 2018

    10, F, 600, 2019, 2019

];

Join(Temp)

LOAD MCl,

MClyr;

LOAD * INLINE [

    Clyr, MCl, MClyr

    2018, 2018, 03

];

NoConcatenate

ts:

LOAD *

Resident Temp

Where Clyr1 <= MCl;

DROP Table Temp;

[imrasyed]

Thanks a lot Sunny,

But only concern whether is it going to be a performance issue?

[sunny_talwar]

Depending on the size of the two tables, your concerns are valid... It might help to know what exactly are you trying to do as there might be another easier way to do it

[imrasyed]

I have first table with 2225 records and the second table with  1 record.

Actually I have 'Time'  table with dates from 2012 to 2019 and the other table 'Month End' which has the latest date.So I am getting the data based on the 'Month End'  column.

[sunny_talwar]

Joining any table with another table which have 1 record should give you no problem whatsoever.... there might be alternatives using FieldValue() function... but I am not really sure I understand this well enough to give you a script for it

Actually I have 'Time'  table with dates from 2012 to 2019 and the other table 'Month End' which has the latest date.So I am getting the data based on the 'Month End'  column.