topic Re: Load data with max date in QlikView

Load data with max date

vchuprina — Wed, 02 Mar 2016 12:26:05 GMT

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.

Re: Load data with max date

Anonymous — Wed, 02 Mar 2016 12:34:11 GMT

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

sunny_talwar — Wed, 02 Mar 2016 12:46:30 GMT

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;

Re: Load data with max date

maxgro — Wed, 02 Mar 2016 13:02:13 GMT

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

Re: Load data with max date

vchuprina — Wed, 02 Mar 2016 16:18:00 GMT

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

Re: Load data with max date

vchuprina — Wed, 02 Mar 2016 17:00:36 GMT

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

sunny_talwar — Wed, 02 Mar 2016 17:28:53 GMT

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

Re: Load data with max date

vchuprina — Thu, 03 Mar 2016 13:40:23 GMT

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%