topic Re: Re: Load script doubt in QlikView

Load script doubt

Mon, 10 Nov 2014 03:27:09 GMT

Hello everyone,

I have data like shown below:

mop	order
3/1/2012	10
4/1/2012	35
5/1/2012	63
6/1/2012	80
7/1/2012	120
8/1/2012	200
9/1/2012	390
10/1/2012	638
11/1/2012	1250
12/1/2012	2599
1/1/2013	3200
2/1/2013	4320
3/1/2013	7980

But i want change like below in my load script:

mop	withinmonth	1month	2month	3month	4month	5month	6month	>6 and <=12month
3/1/2012	10	35	63	80	120	200	390	19987
4/1/2012	35	63	80	120	200	390	638	19349
5/1/2012	63	80	120	200	390	638	1250	18099
6/1/2012	80	120	200	390	638	1250	2599	15500
7/1/2012	120	200	390	638	1250	2599	3200	12300
8/1/2012	200	390	638	1250	2599	3200	4320	7980
9/1/2012	390	638	1250	2599	3200	4320	7980	0
10/1/2012	638	1250	2599	3200	4320	7980	0	0
11/1/2012	1250	2599	3200	4320	7980	0	0	0
12/1/2012	2599	3200	4320	7980	0	0	0	0
1/1/2013	3200	4320	7980	0	0	0	0	0
2/1/2013	4320	7980	0	0	0	0	0	0
3/1/2013	7980	0	0	0	0	0	0	0

Could you please advise me

Best,

Robert

Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 03:34:58 GMT

Hi Robert,

What is your date format? Is it M/D/YYYY?

How do you calculate the last column?

And the last question: Why do you need this? What is the goal?

Re: Load script doubt

Mon, 10 Nov 2014 03:38:42 GMT

i think crosstable(mop,order)

Re: Load script doubt

Mon, 10 Nov 2014 03:45:36 GMT

Hi,

Yes, My date fromat is M/D/YYYY and all date would be month start only.

Best,

Robert

Re: Load script doubt

Mon, 10 Nov 2014 03:59:08 GMT

Hi,

Please find attached the sample Qvw file.

Its very urgent for me so i would be really grateful if anyone give appropriate solution for my question.

Best,

Robert

Re: Load script doubt

sujeetsingh — Mon, 10 Nov 2014 04:04:45 GMT

What i will recommend you is to use peek and previous these two functions are inter record functions that can be used in your scenario.

Re: Load script doubt

Mon, 10 Nov 2014 04:07:52 GMT

Hi,

I am not aware the functions that you suggested me.

Please help me to do it in my sample application.

Best,

Robert

Re: Load script doubt

Eric_Tay — Mon, 10 Nov 2014 04:17:09 GMT

Hi Robert,

Perhaps you can consider using set analysis for each of the column. For example: the expression for 'within month' column can be:

count(

<Month(mop) = Month(Date('1/1/2013','MM/DD/YYYY')>

}

order).

Re: Load script doubt

sujeetsingh — Mon, 10 Nov 2014 04:17:27 GMT

Follow this thread

Peek() vs Previous() – When to Use Each

Re: Load script doubt

Mon, 10 Nov 2014 04:21:46 GMT

Hi,

I want do it in my data model since i want to do relation another table to make another calculation.

I would expected dynamic calculation.

Best,

Robert

Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 04:26:26 GMT

Please try.
[data]:
LOAD mop,
order
FROM
[http://community.qlik.com/thread/141358]
(html, codepage is 1252, embedded labels, table is @1)

;

NoConcatenate
data1:
LOAD *,
IF(isNULL(Previous(Month5)),0,Previous(Month5)) AS Month6;
LOAD *,
IF(isNULL(Previous(Month4)),0,Previous(Month4)) AS Month5;
LOAD *,
IF(isNULL(Previous(Month3)),0,Previous(Month3)) AS Month4;
LOAD *,
IF(isNULL(Previous(Month2)),0,Previous(Month2)) AS Month3;
LOAD *,
IF(isNULL(Previous(Month1)),0,Previous(Month1)) AS Month2;
LOAD mop,
order AS withinmonth,
IF(isNULL(Previous(order)),0,Previous(order)) AS Month1;
LOAD *
Resident data
Order by mop desc;

DROP Table data;

But You didn't answer how to calculate the last column... It's not clear at the moment.

Re: Load script doubt

Mon, 10 Nov 2014 04:32:54 GMT

Hi,

Many Thanks. this is what i expected, the last column sum of(Month7 to Month 12).

Best,

Robert

Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 04:42:24 GMT

It's still unclear. I tried to sum up the numbers and couldn't get the numbers you have.

Could you give examples? If you need it, sure.

Regards,

Sergey

Re: Load script doubt

Mon, 10 Nov 2014 04:52:45 GMT

mop	withinmonth	Month1	Month2	Month3	Month4	Month5	Month6
3/1/2012	10	35	63	80	120	200	390
4/1/2012	35	63	80	120	200	390	638

In order to get >6 and <=12month for 3/1/2012(Mop):

10/1/2012	638	Month7
11/1/2012	1250	Month8
12/1/2012	2599	Month9
1/1/2013	3200	Month10
2/1/2013	4320	Month11
3/1/2013	7980	Month12
19987	>6 and <=12month

In order to get >6 and <=12month for 4/1/2012(Mop):

11/1/2012	1250	Month7
12/1/2012	2599	Month8
1/1/2013	3200	Month9
2/1/2013	4320	Month10
3/1/2013	7980	Month11
19349	>6 and <=12month

Did you get what i mean ?

Best,

Robert

Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 05:22:47 GMT

Hi,

updated script bellow

[data]:
LOAD mop,
order
FROM
[http://community.qlik.com/thread/141358]
(html, codepage is 1252, embedded labels, table is @1)

;

NoConcatenate
data1:
LOAD*,
IF(isNull(Peek('sum')),0,Previous(Month6)+Peek('sum')) AS [sum];
LOAD *,
IF(isNULL(Previous(Month5)),0,Previous(Month5)) AS Month6;
LOAD *,
IF(isNULL(Previous(Month4)),0,Previous(Month4)) AS Month5;
LOAD *,
IF(isNULL(Previous(Month3)),0,Previous(Month3)) AS Month4;
LOAD *,
IF(isNULL(Previous(Month2)),0,Previous(Month2)) AS Month3;
LOAD *,
IF(isNULL(Previous(Month1)),0,Previous(Month1)) AS Month2;
LOAD mop,
order AS withinmonth,
IF(isNULL(Previous(order)),0,Previous(order)) AS Month1;
LOAD *
Resident data
Order by mop desc;

DROP Table data;

Regards,

Sergey

Re: Load script doubt

Mon, 10 Nov 2014 05:46:27 GMT

Hi,

Let me clearly explain my logic for last column:

For each mop, I want to calculate (withinmonth, Month 1 ... Month 6) but for last column that is what i mentioned >6 and <=12month That is suppose to be Month12. The metrics calculation would be cumulative sum of( Month7,Month8,Month9,Month10,Month11,Month12).

In my data model, i no need to calculate Month7,Month8,Month9,Month10,Month11,Month12 but i want sum cumulative of (Month 7 to Month 12).

for 3/1/2012 -mop below is the value for month 7 to Month 12. if you sum all the i can get the Month 12 ..

10/1/2012	638	Month7
11/1/2012	1250	Month8
12/1/2012	2599	Month9
1/1/2013	3200	Month10
2/1/2013	4320	Month11
3/1/2013	7980	Month12

Answer would be for last column of 3/1/2012 - 19987

My sincere apologizes for my previous explanation was not clear for you.

Best,

Robert

Re: Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 05:57:45 GMT

I have exactly same result as you need

PFA

Re: Load script doubt

Mon, 10 Nov 2014 05:58:07 GMT

Many Thanks

Re: Re: Load script doubt

Mon, 10 Nov 2014 06:01:47 GMT

Hi Sergey Makushinsky,

Many Thanks for your solution. its really helps me for one of my vital report.

Best,

Robert

Re: Re: Load script doubt

SergeyMak — Mon, 10 Nov 2014 06:13:46 GMT

Hi Robert,

No worries.

I just have to add that it's better for you to follow this thread for the future

Peek() vs Previous() – When to Use Each

As suggested by sujeetsingh

Regards,

Sergey