How to get data from two tables with several condition
Hi Friends
I am stuck in a situation.
I have 2 tables say T1 and T2
T1:
Id
Date
1
10-Oct-2017
2
12-Dec-2017
3
20-Jun-2018
4
10-Jul-2018
5
11-Jul-2018
6
12-Jul-2018
7
13-Jan-2019
8
14-Feb-2019
9
15-Mar-2019
10
16-Apr-2019
T2:
Id
Amount
Status
1
100
1
1
200
2
1
400
1
2
200
2
5
500
2
5
1000
1
5
200
2
5
800
2
5
200
1
6
200
2
6
200
1
6
600
1
7
200
1
7
200
2
9
3
2
9
200
1
9
200
2
9
200
1
10
200
2
10
200
1
10
200
2
What I need is, if It is current Year, then load all data and if it is previous year(here previous means, all years except current year) then load only those data whose Status=1.
step 1 join the tables together (add a year column for simplicity)
step 2 use noconcatente and resident load previous years i.e. Year<Year(today()) and Status = 1
step 3 resident load current year i.e. Year=Year(today()) and concatenate to table in step 2
IdListTemp:
LOAD
Id,
"Date",
Year("Date") as Year
FROM [lib://Downloads/Sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet2);
left join (IdListTemp)
LOAD
Id,
Amount,
Status
FROM [lib://Downloads/Sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet3);
NoConcatenate
IdList:
load *
resident IdListTemp
where Year< Year(today()) and Status=1;
Concatenate (IdList)
load *
resident IdListTemp
where Year= Year(today()) ;
drop table IdListTemp;
step 1 join the tables together (add a year column for simplicity)
step 2 use noconcatente and resident load previous years i.e. Year<Year(today()) and Status = 1
step 3 resident load current year i.e. Year=Year(today()) and concatenate to table in step 2
IdListTemp:
LOAD
Id,
"Date",
Year("Date") as Year
FROM [lib://Downloads/Sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet2);
left join (IdListTemp)
LOAD
Id,
Amount,
Status
FROM [lib://Downloads/Sample_Data.xlsx]
(ooxml, embedded labels, table is Sheet3);
NoConcatenate
IdList:
load *
resident IdListTemp
where Year< Year(today()) and Status=1;
Concatenate (IdList)
load *
resident IdListTemp
where Year= Year(today()) ;
drop table IdListTemp;
