Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In files I get historical data and current week data. Date information can be in following formats:
8.2.14
2/6/2016
I use alt function to determine type of date information:
Date(Alt(Date#('$(vDate)', 'MM/DD/YYYY'), Date#('$(vDate)', 'MM.DD.YYYY')), 'YYYYMMDD') as WEEK_ENDING_DATE
How can I load only string with max date.
I tried convert date into number and then use Max function, but for some reason it doesn't work and I get script error.
What you are trying to achieve?
Could you elaborate more?
may be like this?
Load
*,
Date(DateField,'DD/MM/YYYY') as DateField
From Table
where
DateField=Date(max(DateField),'DD/MM/YYYY');
May be this:
Table:
LOAD yourFields,
Date(Alt(Date#(DateField, 'M/D/YYYY'), Date#(DateField, 'M.D.YYYY')), 'YYYYMMDD') as WEEK_ENDING_DATE
FROM Table:
Right Join (Table)
LOAD Max(WEEK_ENDING_DATE) as WEEK_ENDING_DATE
Resident Table;
// test data
Source:
load * inline [
DateField
8.2.14
2/6/2016
8.2.15
6/30/2016
7/1/2016
];
// add a date field
Left Join (Source)
LOAD
DateField,
Date( (Alt(Date#(DateField, 'M/D/YYYY'), Date#(DateField, 'M.D.YYYY'))), 'YYYYMMDD') as DateNew
Resident Source;
// keep the max
Right Keep (Source)
LOAD
Date( Max(DateNew), 'YYYYMMDD') as DateNew
Resident Source;
I try to load data from file that contains historical data, but I need only last week with correct data.
In example below I should load only week 2/6/2016
Date | LYS $ | TYS $ | S% | TYO $ | O% |
1.19.13 | 24.2 | 26 | -7% | 13.6 | 78% |
7/26/2014 | 42.8 | 26 | 65% | 22 | 95% |
8.2.14 | -7.1 | 26 | -127% | 34 | -121% |
2/6/2016 | 38.6 | 33.7 | 15% | 22.6 | 71% |
2/13/2016 | #DIV/0! | #DIV/0 |
Hi Sunny,
I tried your solution and it works butwith some questions.
In my script I process data from different types of files and one of these types contains history data.
When I added right join I got max week but for all files, I can't understand why this happened because I use if else statement .
For example:
IF Wildmatch (vSheet, '1') then
Data :
Load * from ...
ELSEIF Wildmatch (vSheet, '2') then
Data :
Load * from ...
ELSEIF Wildmatch (vSheet, '3') then
Data :
Load * from ...
Right Join (Data)
LOAD Max(WEEK_ENDING_DATE) as WEEK_ENDING_DATE
Resident Data ;
EndIF
I thought that I will load data with max week from files with sheet '3', but in result I got only files that have current max week(02/27) in file other file are ignored by script.
I am not 100% certain what you are trying to do right now and also having difficulty in understanding the required output.
Hi Sunny,
In output I want to see data for max data from each file, for example if file have such data inside:
Date | LYS $ | TYS $ | S% | TYO $ | O% |
1.19.13 | 24.2 | 26 | -7% | 13.6 | 78% |
7/26/2014 | 42.8 | 26 | 65% | 22 | 95% |
8.2.14 | -7.1 | 26 | -127% | 34 | -121% |
2/6/2016 | 38.6 | 33.7 | 15% | 22.6 | 71% |
2/13/2016 | #DIV/0! | #DIV/0 |
I want to see following in output file:
Filename | Date | LYS $ | TYS $ | S% | TYO $ | O% |
File1 | 2/6/2016 | 38.6 | 33.7 | 15% | 22.6 | 71% |