Solved: Re: Make Date - Qlik Community

Report Inappropriate Content · ‎2014-05-06

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 · ‎2014-05-06

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.

View solution in original post

jagan · ‎2014-05-06

Hi Ankit,

Try this expression and use Date and Name as Dimensions

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

Regards,

Jagan.

Report Inappropriate Content · ‎2014-05-06

Hi Jagan,

It says error in expression and giving no result.

Thanks

Ankit

Report Inappropriate Content · ‎2014-05-06

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

I think a minor typo.

CELAMBARASAN · ‎2014-05-06

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

Report Inappropriate Content · ‎2014-05-06

Hi Ram,

Same error.

Thanks

Ankit

Report Inappropriate Content · ‎2014-05-06

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 · ‎2014-05-06

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 · ‎2014-05-06

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 · ‎2014-05-06

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

1. aggr(max(Date),Name)

2.Name

expression :

Sum (Value)