Make Date

[#1]

Hi,

Please help me with the following task:

File name is 032014.

I have Data as :

Name,

     [1] as [01],

     [2] as [02],

     [3] as [03],

     [4] as [04],

     [5] as [05],

     [6] as [06],

     [7] as [07],

     [8] as [08],

     [9] as [09],

     [10],

     [11],

     [12],

     [13],

     [14],

     [15],

     [16],

     [17],

     [18],

     [19],

     [20],

     [21],

     [22],

     [23],

     [24],

     [25],

     [26],

     [27],

     [28],

     [29],

     [30],

     [31]

Note: Day pick from top header

  Month&year pick from filename

And my Requirement is :

In a straight table :Max(Date),Name, Value(Corresponding value according to the Max (Date). 

For example:

Date	Name	Value
9-Mar-2014	A	400
9-Mar-2014	B	401
9-Mar-2014	C	402
9-Mar-2014	D	403
9-Mar-2014	F	404

Thanks

Ankit

[jagan]

Hi Ankit,

Try this expression and use Date and Name as Dimensions

=Sum({Date={"$(=Date(Max(Date), 'D-MMM-YYYY'))"}}Value)

Regards,

Jagan.

[#3]

Hi Jagan,

It says error in expression and giving no result.

Thanks

Ankit

[#4]

=sum({<Date={"$(=Date(Max(Date),'D-MMM-YYYY'))"}>}Value)

I think a minor typo.

[CELAMBARASAN]

Did you formed the date field using the top header and file name?

[#6]

Hi Ram,

Same error.

Thanks

Ankit

[#7]

Hi,

Yes I have done this till now.

Tab1:

CrossTable(day, Values,2)

LOAD  left(Filename(), 6) as Filedate,

     Name,

     [1] as [01],

     [2] as [02],

     [3] as [03],

     [4] as [04],

     [5] as [05],

     [6] as [06],

     [7] as [07],

     [8] as [08],

     [9] as [09],

     [10],

     [11],

     [12],

     [13],

     [14],

     [15],

     [16],

     [17],

     [18],

     [19],

     [20],

     [21],

     [22],

     [23],

     [24],

     [25],

     [26],

     [27],

     [28],

     [29],

     [30],

     [31]

FROM

(ooxml, embedded labels, table is Data);

Load

Values,

Name,

Date(Max(Date(date#(day &''& Filedate,'DDMMYYY'),'DD/MM/YYYY'))) AS DATE

Resident Tab1

Group By Name,Values;

Drop Table Tab1;

Thanks

[jagan]

Hi,

Can you attach some sample file?  I think you need to use CrossTable to transform the data and then arrive a date field.

Regards,

Jagan.

[jagan]

Tab1:

CrossTable(day, Values,2)

LOAD  left(Filename(), 6) as Filedate,

     Name,

     [1] as [01],

     [2] as [02],

     [3] as [03],

     [4] as [04],

     [5] as [05],

     [6] as [06],

     [7] as [07],

     [8] as [08],

     [9] as [09],

     [10],

     [11],

     [12],

     [13],

     [14],

     [15],

     [16],

     [17],

     [18],

     [19],

     [20],

     [21],

     [22],

     [23],

     [24],

     [25],

     [26],

     [27],

     [28],

     [29],

     [30],

     [31]

FROM

(ooxml, embedded labels, table is Data);

Data:

LOAD

*,

MakeDate(Right(Filedate, 4), Left(Filedate, 2), day) Date

RESIDENT Tab1;

DROP TABLE Tab1;

Now in chart Try this expression and use Date and Name as Dimensions

=Sum({<Date={"$(=Date(Max(Date)))"}>}Value)

Regards,

Jagan.

[preminqlik]

yes after doing cross table do this , add  dimensions like below :

1. aggr(max(Date),Name)

2.Name

expression :

Sum (Value)