topic Re: Re: Re: Help required for calculation in chart for date difference in QlikView

Help required for calculation in chart for date difference

Wed, 05 Nov 2014 09:23:27 GMT

Dear Expert,

Need help for EQUNR_S to get Act_Typ difference for same field Doc-Date.

How to calculate the same in chart or while loading.

ID	Ship_Key	Doc_Date	EQUNR_S	Act_Typ	get date difference
1	0000000001000101397Z20	01.02.2011	1397	ER	0
13	0000000002000101397Z30	01.02.2011	1397	E	0.00
10076	0000001797000201397Z30	10.06.2011	1397	F	129.00
10078	0000001798000201397Z30	13.06.2011	1397	D	3.00
10080	0000001799000201397Z20	13.06.2011	1397	T	0.00
11256	0000002262000101397Z20	16.07.2011	1397	ER	33.00
13189	0000003284000101397Z30	29.10.2011	1397	I	105.00

Thanks

Ashif

Re: Help required for calculation in chart for date difference

Gysbert_Wassenaar — Wed, 05 Nov 2014 09:45:06 GMT

Maybe like this:

LOAD

ID,

Ship_Key,

Doc_Date,

EQUNR_S,

Act_Typ,

If(EQUNR_S=previous(EQUNR_S),Doc_Date - previous(Doc_Date),0) as [get date difference]

FROM

...somewhere...

;

Re: Re: Help required for calculation in chart for date difference

Wed, 05 Nov 2014 11:16:18 GMT

Dear Gysbert,

Above data is sor

Its giving 0 value in load statement as mentioned by you.

Thanks

Ashif

Re: Help required for calculation in chart for date difference

Gysbert_Wassenaar — Wed, 05 Nov 2014 11:30:23 GMT

Your Doc_Date field contains only one value. Every record has the same Doc_Date value, so there's never a difference between the Doc_Date value of one record and the Doc_Date value of another record.

Re: Re: Help required for calculation in chart for date difference

Wed, 05 Nov 2014 12:23:38 GMT

I've loaded all the records with example. Check the same. There is difference in date.

Re: Help required for calculation in chart for date difference

Gysbert_Wassenaar — Wed, 05 Nov 2014 13:07:51 GMT

I think the problem is that your data is not sorted by EQUNR_S first. You can resolve that using a resident load with an order by clause.

Temp:

LOAD * FROM YourQVD (qvd);

Result:

LOAD *, if(previous(EQUNR_S=EQUNR_S, Doc_Date - previous(Doc_Date),0) as TT;

Resident Temp

Order By EQUNR_S, Doc_Date;

Re: Help required for calculation in chart for date difference

jagan — Thu, 06 Nov 2014 05:26:16 GMT

Hi,

Can you post the entire script, are you using any aggregate functions like Sum(), Count(), Max(), Min() etc. If you are using then use Group by in script like below

Load EQUNR_S,

Doc_Date,

Sum(SomeField) AS Total

FROM DataSource

Group by EQUNR_S, Doc_Date; -- You have to specify all the fields here given in the Load.

Hope this helps you.

Regards,

Jagan.

Re: Re: Help required for calculation in chart for date difference

Thu, 06 Nov 2014 05:29:41 GMT

Now i want the chart to appear like this

ID	EQUNR	Act_Typ	Sender	Receiver	Doc_Type	Doc_Date	Operation time
2015	3	ER		0000002948		01/02/2011
2169	3	E	0000002948	0000002948		01/02/2011	282
14511	3	F	0000002948	0000002948		10/11/2011	296
25160	3	D	0000002948	0000002948		01/09/2012	30
25667	3	ER	0000003398	0000003398		01/10/2012	0
25668	3	C	0000003398	0000003398		01/10/2012	0
25668	3	C	0000003398	0000003398		01/10/2012	0
25666	3	T	0000002948	0000003398		01/10/2012

Help me with chart expression to get the result as current result is getting like this.

EQUNR	Act_Typ	Operation time
3	D	-578
3	F	326

Re: Re: Help required for calculation in chart for date difference

jagan — Thu, 06 Nov 2014 05:51:15 GMT

Hi,

Add one more column in script like below

Result:

LOAD *, if(previous(EQUNR_S=EQUNR_S, Doc_Date - previous(Doc_Date),0) as TT,

if(previous(EQUNR_S<>EQUNR_S, 1, 0) AS Flag,

if(previous(EQUNR_S=EQUNR_S, RangeSum(Peek([TT]), Doc_Date - previous(Doc_Date)), (Doc_Date - previous(Doc_Date)) AS AccumSum

Resident Temp

Order By EQUNR_S, Doc_Date;

Now in front end

Chart : Straight Table

Dimension: EQUNR, Act_Typ

Expression: Sum({<Flag ={1}>}AccumSum)

Hope this helps you.

Regards,

Jagan.

Re: Re: Re: Help required for calculation in chart for date difference

Thu, 06 Nov 2014 06:07:25 GMT

Still not gettong the desired result.

ID	EQUNR_S	Act_Typ	Doc_Date
2015	3	ER	01/02/2011	0
2169	3	E	01/02/2011	282
14511	3	F	10/11/2011	296
25160	3	D	01/09/2012	30
25666	3	T	01/10/2012	0
25667	3	ER	01/10/2012	0
25668	3	C	01/10/2012	0

Re: Re: Re: Help required for calculation in chart for date difference

jagan — Thu, 06 Nov 2014 06:13:23 GMT

HI,

Try like this

Temp:

LOAD *, if(previous(EQUNR_S=EQUNR_S, Doc_Date - previous(Doc_Date),0) as TT,

if(previous(EQUNR_S<>EQUNR_S, 1, 0) AS Flag,

if(previous(EQUNR_S=EQUNR_S, RangeSum(Peek([TT]), Doc_Date - previous(Doc_Date)), (Doc_Date - previous(Doc_Date)) AS AccumSum

Resident Temp

Order By EQUNR_S, Doc_Date;

Result:

NoConcatenate

LOAD *,

if(previous(EQUNR_S<>EQUNR_S, 1, 0) AS Flag

Resident Temp

Order By EQUNR_S, Doc_Date DESC;

Now in front end

Chart : Straight Table

Dimension: EQUNR, Act_Typ

Expression: Sum({<Flag ={1}>}AccumSum)

REgards,

Jagan.