Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

Load data with max date

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
7 Replies
Anonymous
Not applicable

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');

sunny_talwar

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;

maxgro
MVP
MVP

// 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;

vchuprina
Specialist
Specialist
Author

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.1324.226-7%13.678%
7/26/201442.82665%2295%
8.2.14-7.126-127%34-121%
2/6/201638.633.715%22.671%
2/13/2016 #DIV/0! #DIV/0
Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
vchuprina
Specialist
Specialist
Author

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
sunny_talwar

I am not 100% certain what you are trying to do right now and also having difficulty in understanding the required output.

vchuprina
Specialist
Specialist
Author

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.1324.226-7%13.678%
7/26/201442.82665%2295%
8.2.14-7.126-127%34-121%
2/6/201638.633.715%22.671%
2/13/2016#DIV/0!#DIV/0

I want to see  following in output file:

 

FilenameDate  LYS $TYS $S%TYO $O%
File12/6/201638.633.715%22.671%
Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").