Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vchuprina
New Contributor III

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.

7 Replies
balrajahlawat
Esteemed Contributor

Re: Load data with max date

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

Re: Load data with max date

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;

MVP
MVP

Re: Load data with max date

// 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
New Contributor III

Re: Load data with max date

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
vchuprina
New Contributor III

Re: Load data with max date

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.

Re: Load data with max date

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

vchuprina
New Contributor III

Re: Load data with max date

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%