topic Re: Make Date in QlikView

Make Date

Tue, 06 May 2014 06:29:39 GMT

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

Re: Make Date

jagan — Tue, 06 May 2014 06:37:16 GMT

Hi Ankit,

Try this expression and use Date and Name as Dimensions

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

Regards,

Jagan.

Re: Make Date

Tue, 06 May 2014 06:44:43 GMT

Hi Jagan,

It says error in expression and giving no result.

Thanks

Ankit

Re: Make Date

Tue, 06 May 2014 06:49:41 GMT

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

I think a minor typo.

Re: Make Date

CELAMBARASAN — Tue, 06 May 2014 06:53:23 GMT

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

Re: Make Date

Tue, 06 May 2014 06:54:58 GMT

Hi Ram,

Same error.

Thanks

Ankit

Re: Make Date

Tue, 06 May 2014 06:56:43 GMT

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

Re: Make Date

jagan — Tue, 06 May 2014 06:58:12 GMT

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.

Re: Make Date

jagan — Tue, 06 May 2014 07:02:21 GMT

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.

Re: Make Date

preminqlik — Tue, 06 May 2014 07:02:38 GMT

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

1. aggr(max(Date),Name)

2.Name

expression :

Sum (Value)

Re: Make Date

Tue, 06 May 2014 07:17:50 GMT

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

Re: Make Date

jagan — Tue, 06 May 2014 08:02:42 GMT

Hi,

Did you tried the script in my previous reply?

Regards.

jagan.

Re: Make Date

Tue, 06 May 2014 08:22:39 GMT

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.

Re: Make Date

CELAMBARASAN — Tue, 06 May 2014 09:34:47 GMT

Dimensions: Name, Value

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

Re: Make Date

jagan — Tue, 06 May 2014 10:11:08 GMT

Hi,

Can you attach the sample file and expected output.

Regards,

Jagan.

Re: Make Date

Tue, 06 May 2014 10:18:18 GMT

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

Re: Re: Make Date

jagan — Tue, 06 May 2014 10:44:17 GMT

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.

Re: Make Date

Tue, 06 May 2014 10:59:20 GMT

Hi Jagan,

Finally it worked and thanks a lot for helping.

Thanks

Ankit