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

[#2]

Name	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
A	10	20	30	100	121	131	200	300	400	14	24	34	104	125	135	204	304	404	13	23	33	103	124	134	203	303	403	12	22	32	102
B	11	21	31	101	122	132	201	301	401	123	133	202	302	402	104	125	135	204	304	404	13	23	33	103	124	30	100	121	131	200	300
C	12	22	32	102	123	133	202	302	402	10	20	30	100	121	131	32	102	123	133	202	302	402	14	24	34	104	125	135	204	20	30
D	13	23	33	103	124	134	203	303	403	12	22	32	102	123	133	122	132	201	301	401	123	125	135	204	304	404	133	122	132	201	301
F	14	24	34	104	125	135	204	304	404	10	20	30	100	121	131	33	103	124	134	203	303	403	104	125	135	204	304	404	13	23	202

[jagan]

Hi,

Did you tried the script in my previous  reply?

Regards.

jagan.

[#4]

Hi Jagan,

Yes, I have tried your script and its not showing the correct filedate and showing sum(values).

My requirement is now changed,

Please help me with that:

I want to show

Name     Value   Max(Date)

and the value to be corresponding to the Max(Date)

eg: A     400    31-03-2014.

[CELAMBARASAN]

Dimensions: Name, Value

Expression: Only({<Date={'$(=Date(Max(Date)))'}>} Date)

[jagan]

Hi,

Can you attach the sample file and expected output.

Regards,

Jagan.

[#7]

Hi Jagan,

Can't attach the file however will try to explain what is data and requirement.

Filename is 032014

Below is the Data:

Name	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
A	10	20	30	100	121	131	200	300	400	14	24	34	104	125	135	204	304	404	13	23	33	103	124	134	203	303	403	12	22	32	102
B	11	21	31	101	122	132	201	301	401	123	133	202	302	402	104	125	135	204	304	404	13	23	33	103	124	30	100	121	131	200	300
C	12	22	32	102	123	133	202	302	402	10	20	30	100	121	131	32	102	123	133	202	302	402	14	24	34	104	125	135	204	20	30
D	13	23	33	103	124	134	203	303	403	12	22	32	102	123	133	122	132	201	301	401	123	125	135	204	304	404	133	122	132	201	301
F	14	24	34	104	125	135	204	304	404	10	20	30	100	121	131	33	103	124	134	203	303	403	104	125	135	204	304	404	13	23	202

Note:

Day pick from top header

Month&year pick from filename

Output required is :

Date	Name	Value(According to MAX(Date)
Max date	A	MAX Date Value
Max date	B	MAX Date Value
Max date	C	MAX Date Value
Max date	D	MAX Date Value
Max date	F	MAX Date Value

Thanks for being so patient.

Ankit

[jagan · Accepted solution]

Hi,

Please find attached file for solution.

Script used in file

Tab1:

CrossTable(Day, Value, 2)

LOAD

  FileName() AS FileName,

Name,

    [1],

    [2],

    [3],

    [4],

    [5],

    [6],

    [7],

    [8],

    [9],

    [10],

    [11],

    [12],

    [13],

    [14],

    [15],

    [16],

    [17],

    [18],

    [19],

    [20],

    [21],

    [22],

    [23],

    [24],

    [25],

    [26],

    [27],

    [28],

    [29],

    [30],

    [31]

FROM

[032014.xlsx]

(ooxml, embedded labels, table is Sheet1);

Data:

LOAD

*,

Date(Date#(Mid(FileName, 3,4) & '-'  & Num(Left(FileName, 2)) & '-'  & Day, 'YYYY-M-D')) as Date

RESIDENT Tab1;

DROP TABLE Tab1;

Dimensions : Name, Date

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

or =Aggr(If(Max(TOTAL<Name> Date) = Date, Sum(Value)), Name, Date)

Regards,

Jagan.

[#9]

Hi Jagan,

Finally it worked and thanks a lot for helping.

Thanks

Ankit