Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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
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
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.
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;
Thanks a lot Sunny,
But only concern whether is it going to be a performance issue?
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
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.
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.