topic Re: script help in QlikView

script help

Fri, 15 May 2015 13:44:47 GMT

Hi,

I have two field i.e. - Invoice Date and Amount, I want this

Invoice Date	Amount	Output -->	Invoice Date	Amount
01/01/2015	1		31/01/2015	301
02/01/2015	11		28/02/2015	581
03/01/2015	21		31/03/2015	891
04/01/2015	31		30/04/2015	1191
05/01/2015	41		01/05/2015	1201
06/01/2015	51		02/05/2015	1211
07/01/2015	61		03/05/2015	1221
08/01/2015	71		04/05/2015	1231
09/01/2015	81		05/05/2015	1241
10/01/2015	91		06/05/2015	1251
11/01/2015	101		07/05/2015	1261
12/01/2015	111		08/05/2015	1271
13/01/2015	121		09/05/2015	1281
14/01/2015	131		10/05/2015	1291
15/01/2015	141		11/05/2015	1301
16/01/2015	151		12/05/2015	1311
17/01/2015	161		13/05/2015	1321
18/01/2015	171		14/05/2015	1331
19/01/2015	181		15/05/2015	1341
20/01/2015	191
21/01/2015	201
22/01/2015	211
23/01/2015	221
24/01/2015	231
25/01/2015	241
26/01/2015	251
27/01/2015	261
28/01/2015	271
29/01/2015	281
30/01/2015	291
31/01/2015	301
01/02/2015	311
02/02/2015	321
03/02/2015	331
04/02/2015	341
05/02/2015	351
06/02/2015	361
07/02/2015	371
08/02/2015	381
09/02/2015	391
10/02/2015	401
11/02/2015	411
12/02/2015	421
13/02/2015	431
14/02/2015	441
15/02/2015	451
16/02/2015	461
17/02/2015	471
18/02/2015	481
19/02/2015	491
20/02/2015	501
21/02/2015	511
22/02/2015	521
23/02/2015	531
24/02/2015	541
25/02/2015	551
26/02/2015	561
27/02/2015	571
28/02/2015	581
01/03/2015	591
02/03/2015	601
03/03/2015	611
04/03/2015	621
05/03/2015	631
06/03/2015	641
07/03/2015	651
08/03/2015	661
09/03/2015	671
10/03/2015	681
11/03/2015	691
12/03/2015	701
13/03/2015	711
14/03/2015	721
15/03/2015	731
16/03/2015	741
17/03/2015	751
18/03/2015	761
19/03/2015	771
20/03/2015	781
21/03/2015	791
22/03/2015	801
23/03/2015	811
24/03/2015	821
25/03/2015	831
26/03/2015	841
27/03/2015	851
28/03/2015	861
29/03/2015	871
30/03/2015	881
31/03/2015	891
01/04/2015	901
02/04/2015	911
03/04/2015	921
04/04/2015	931
05/04/2015	941
06/04/2015	951
07/04/2015	961
08/04/2015	971
09/04/2015	981
10/04/2015	991
11/04/2015	1001
12/04/2015	1011
13/04/2015	1021
14/04/2015	1031
15/04/2015	1041
16/04/2015	1051
17/04/2015	1061
18/04/2015	1071
19/04/2015	1081
20/04/2015	1091
21/04/2015	1101
22/04/2015	1111
23/04/2015	1121
24/04/2015	1131
25/04/2015	1141
26/04/2015	1151
27/04/2015	1161
28/04/2015	1171
29/04/2015	1181
30/04/2015	1191
01/05/2015	1201
02/05/2015	1211
03/05/2015	1221
04/05/2015	1231
05/05/2015	1241
06/05/2015	1251
07/05/2015	1261
08/05/2015	1271
09/05/2015	1281
10/05/2015	1291
11/05/2015	1301
12/05/2015	1311
13/05/2015	1321
14/05/2015	1331
15/05/2015	1341

Re: script help

Gysbert_Wassenaar — Fri, 15 May 2015 15:21:16 GMT

Load [Invoice Date], Amount

From ...source....

Where floor([Invoice Date]) = floor(monthend([Invoice Date]));

Re: script help

buzzy996 — Fri, 15 May 2015 15:33:15 GMT

PFA,but u can try for some better expression as well.

Re: script help

petter — Fri, 15 May 2015 15:52:10 GMT

This might be exactly want you are looking for - Need a calculated dimension with an If() :

Re: script help

sona_sa — Fri, 15 May 2015 17:40:00 GMT

Hi Petter,

I need the same in script.

1. Suppose I have 6 month data from 1st Jan 2015 to 1 July 2015 with amount. Then I want from Jan to May 2015, the day will come for end of the month date. Like 31st Jan, 28th Feb, 31 Mar, 30th Apr and 31st May. And for June month entire date will come, for 1st July 2015 will not come.

2. But if 2nd July is available then 31st Jan, 28th Feb, 31 Mar, 30th Apr, 31st May, 30th June, 1st July and 2nd July will be there. Similarly, it will continue and till 30 it will continue again for next month when 1st will come then again point 1 will apply.

Thanks for Reply.

Re: script help

sona_sa — Fri, 15 May 2015 17:44:02 GMT

Hi Gysbert,

It will give every monthend date. But requirement is like this.

I need the same in script.

1. Suppose I have 6 month data from 1st Jan 2015 to 1 July 2015 with amount. Then I want from Jan to May 2015, the day will come for end of the month date. Like 31st Jan, 28th Feb, 31 Mar, 30th Apr and 31st May. And for June month entire date will come, for 1st July 2015 will not come.

2. But if 2nd July is available then 31st Jan, 28th Feb, 31 Mar, 30th Apr, 31st May, 30th June, 1st July and 2nd July will be there. Similarly, it will continue and till 30 it will continue again for next month when 1st will come then again point 1 will apply.

Thanks for reply and sorry I did not check that, so i have applied 2 times. It was not my intention.

Re: script help

manojkulkarni — Fri, 15 May 2015 18:01:10 GMT

can do a group by on date field.

Load max(datefield), amount resident table group by datefield

Re: script help

sona_sa — Fri, 15 May 2015 18:09:32 GMT

Max function will return max date of every month.

Giving you a example :

For fiscal year Jan to current month - May is going. today is 15 - May 2015.

I want from Jan to April it should to return monthend date and for current month may 1st to 14th May date will also return.

Re: script help

Gysbert_Wassenaar — Fri, 15 May 2015 18:35:30 GMT

Ok, you're forgiven

But please have a bit more patience in the future.

See attached qvw for a script based solution.

Re: script help

sona_sa — Fri, 15 May 2015 19:00:20 GMT

Hi Gysbert,

Thanks for reply.

Please find the attached qvw. In the same scenerio if 1st June is coming it is stoping and returning me for Jan to May monthend date.

But output will be - From Jan to Apr - Monthend date of jan / feb / march / apr and for may month from 1st to 31st date will come and for June no date will come. But suppose 2nd june or so on date is coming,

From Jan to May - monthend date but for June till date data will be available.

Thanks Again.

Re: script help

petter — Fri, 15 May 2015 19:44:48 GMT

Ok ... then this should be a script solution that also caters for a single first month Amount

Re: script help

sona_sa — Sat, 16 May 2015 04:43:04 GMT

Hi Peter,

Please find the attached qvw. Now 1st June 2015 should not to come in the same scenario. Here 1st is coming.

Also can we do it by using any variable or nested if or by for loop.

Thanks for reply.

Re: script help

Gysbert_Wassenaar — Sat, 16 May 2015 05:44:04 GMT

I don't understand why you reposted my example and restated your requirements that as I understand them are already implemented in the example.

Please find the attached qvw. In the same scenerio if 1st June is coming it is stoping and returning me for Jan to May monthend date.

Yes, that's what you asked for and that's what my example shows you. And the qvw file you posted shows you exactly this.

But output will be - From Jan to Apr - Monthend date of jan / feb / march / apr and for may month from 1st to 31st date will come and for June no date will come. But suppose 2nd june or so on date is coming,

That's why my example does and what the attachment you posted above does too.

From Jan to May - monthend date but for June till date data will be available.

Of course not, because that's what you asked for.

Can you explain where I'm misunderstanding your requirements?

Re: script help

sona_sa — Sat, 16 May 2015 06:54:38 GMT

Yes Sure Gysbert,

If date is available till 1st of any month from Jan to Dec. 01-Month-Year will not display and for previous month It will give entire month date and prior to prior month it will return only monthend date. But suppose if 2 or 3 or so on is available then it will return previous month --> Monthend Date and for current month till date.

Below are my Input and Output.

Input Invoice Date	Output -->	Onput Invoice Date	Input Invoice Date	Output -->	Onput Invoice Date
1/1/2015		1/31/2015	1/1/2015		1/31/2015
1/2/2015		2/28/2015	1/2/2015		2/28/2015
1/3/2015		3/31/2015	1/3/2015		3/31/2015
1/4/2015		4/1/2015	1/4/2015		4/30/2015
1/5/2015		4/2/2015	1/5/2015		5/1/2015
1/6/2015		4/3/2015	1/6/2015		5/2/2015
1/7/2015		4/4/2015	1/7/2015		5/3/2015
1/8/2015		4/5/2015	1/8/2015
1/9/2015		4/6/2015	1/9/2015
1/10/2015		4/7/2015	1/10/2015
1/11/2015		4/8/2015	1/11/2015
1/12/2015		4/9/2015	1/12/2015
1/13/2015		4/10/2015	1/13/2015
1/14/2015		4/11/2015	1/14/2015
1/15/2015		4/12/2015	1/15/2015
1/16/2015		4/13/2015	1/16/2015
1/17/2015		4/14/2015	1/17/2015
1/18/2015		4/15/2015	1/18/2015
1/19/2015		4/16/2015	1/19/2015
1/20/2015		4/17/2015	1/20/2015
1/21/2015		4/18/2015	1/21/2015
1/22/2015		4/19/2015	1/22/2015
1/23/2015		4/20/2015	1/23/2015
1/24/2015		4/21/2015	1/24/2015
1/25/2015		4/22/2015	1/25/2015
1/26/2015		4/23/2015	1/26/2015
1/27/2015		4/24/2015	1/27/2015
1/28/2015		4/25/2015	1/28/2015
1/29/2015		4/26/2015	1/29/2015
1/30/2015		4/27/2015	1/30/2015
1/31/2015		4/28/2015	1/31/2015
2/1/2015		4/29/2015	2/1/2015
2/2/2015		4/30/2015	2/2/2015
2/3/2015			2/3/2015
2/4/2015			2/4/2015
2/5/2015			2/5/2015
2/6/2015			2/6/2015
2/7/2015			2/7/2015
2/8/2015			2/8/2015
2/9/2015			2/9/2015
2/10/2015			2/10/2015
2/11/2015			2/11/2015
2/12/2015			2/12/2015
2/13/2015			2/13/2015
2/14/2015			2/14/2015
2/15/2015			2/15/2015
2/16/2015			2/16/2015
2/17/2015			2/17/2015
2/18/2015			2/18/2015
2/19/2015			2/19/2015
2/20/2015			2/20/2015
2/21/2015			2/21/2015
2/22/2015			2/22/2015
2/23/2015			2/23/2015
2/24/2015			2/24/2015
2/25/2015			2/25/2015
2/26/2015			2/26/2015
2/27/2015			2/27/2015
2/28/2015			2/28/2015
3/1/2015			3/1/2015
3/2/2015			3/2/2015
3/3/2015			3/3/2015
3/4/2015			3/4/2015
3/5/2015			3/5/2015
3/6/2015			3/6/2015
3/7/2015			3/7/2015
3/8/2015			3/8/2015
3/9/2015			3/9/2015
3/10/2015			3/10/2015
3/11/2015			3/11/2015
3/12/2015			3/12/2015
3/13/2015			3/13/2015
3/14/2015			3/14/2015
3/15/2015			3/15/2015
3/16/2015			3/16/2015
3/17/2015			3/17/2015
3/18/2015			3/18/2015
3/19/2015			3/19/2015
3/20/2015			3/20/2015
3/21/2015			3/21/2015
3/22/2015			3/22/2015
3/23/2015			3/23/2015
3/24/2015			3/24/2015
3/25/2015			3/25/2015
3/26/2015			3/26/2015
3/27/2015			3/27/2015
3/28/2015			3/28/2015
3/29/2015			3/29/2015
3/30/2015			3/30/2015
3/31/2015			3/31/2015
4/1/2015			4/1/2015
4/2/2015			4/2/2015
4/3/2015			4/3/2015
4/4/2015			4/4/2015
4/5/2015			4/5/2015
4/6/2015			4/6/2015
4/7/2015			4/7/2015
4/8/2015			4/8/2015
4/9/2015			4/9/2015
4/10/2015			4/10/2015
4/11/2015			4/11/2015
4/12/2015			4/12/2015
4/13/2015			4/13/2015
4/14/2015			4/14/2015
4/15/2015			4/15/2015
4/16/2015			4/16/2015
4/17/2015			4/17/2015
4/18/2015			4/18/2015
4/19/2015			4/19/2015
4/20/2015			4/20/2015
4/21/2015			4/21/2015
4/22/2015			4/22/2015
4/23/2015			4/23/2015
4/24/2015			4/24/2015
4/25/2015			4/25/2015
4/26/2015			4/26/2015
4/27/2015			4/27/2015
4/28/2015			4/28/2015
4/29/2015			4/29/2015
4/30/2015			4/30/2015
5/1/2015			5/1/2015
			5/2/2015
			5/3/2015

Re: script help

Gysbert_Wassenaar — Sat, 16 May 2015 07:05:15 GMT

If date is available till 1st of any month from Jan to Dec. 01-Month-Year will not display

What do you mean '01-Month-Year will not display? What does that mean?

and for previous month It will give entire month date

What do you mean 'give entire month date'? What is a 'month date'? And what is the difference between an entire one and a partial one?

and prior to prior month it will return only monthend date.

Prior to prior month? Does that mean not the previous month, but the month before that?

But suppose if 2 or 3 or so on is available then it will return previous month --> Monthend Date and for current month till date.

And how is this different from the implementations that we already posted? Please show me how the example output you posted above is different from the results in the examples and screenshots I posted.

Re: script help

sona_sa — Sat, 16 May 2015 07:10:54 GMT

Hi Gysbert,

Please find the attached xls, I have mention with example in both scenario, But output should to return. I can understand that I am not explaining verbally, But by xls. hope you are able to understand the same.

Thanks.

Re: script help

preminqlik — Sat, 16 May 2015 07:28:04 GMT

hi there ,

u need to create set expression values wrt it like below

sum({<Month=,InvoiceDate={$(=Concat({<Year={$(=max(Year))},Month={"<$(=max(Month))"}>} distinct chr(39)&Date(num(floor(MonthEnd(InvoiceDate))),'DD/MM/YYYY')&chr(39),','))}+

{$(=Concat(distinct aggr(nodistinct only({<Year={$(=max(Year))},Month={$(=maxstring(Month))}>}chr(39)&Date(InvoiceDate,'DD/MM/YYYY')&Chr(39)),InvoiceDate),','))}

>}Amount)

find the attaachment, Hope this helps you

Re: script help

petter — Sat, 16 May 2015 08:03:35 GMT

Are you seriously trying to solve it yourself? It seems to me that you are only testing everything that people are suggesting to you without doing any contribution except testing and feedback. I don't consider this the right way of using this forum. Make a serious effort to make it work - with at least a minor effort to get it to work and then come back to us.

Re: script help

Gysbert_Wassenaar — Sat, 16 May 2015 08:04:56 GMT

I'm sorry, but I give up. Your excel file shows exactly the kind of output my example does. You can't explain why my solution does not meet your requirements. Good luck.

Re: script help

sona_sa — Sat, 16 May 2015 08:45:49 GMT

Here in Input Invoice Date Date is till 1st May 2015 - OutPut is for Apr Month Entire date is available and prior month (Jan / Feb / Mar) -- Monthend Date is coming.

In Fig. 2 --> In Input Invoice Date after 1st May there is Date available for 2nd or 3rd, So Output is for previous month is Monthend Date but for current month entire 3 date is available.

Means whenever if new month come or first day of month is available for current month the it will return Output 1 else output 2.

If maxdate is 1 then return me previous month entire date else return me till date and for previous month - monthend date.

Hope you got my point.