Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load script doubt

Hello everyone,

I have data like shown below:

moporder
3/1/201210
4/1/201235
5/1/201263
6/1/201280
7/1/2012120
8/1/2012200
9/1/2012390
10/1/2012638
11/1/20121250
12/1/20122599
1/1/20133200
2/1/20134320
3/1/20137980

But i want change like below in my load script:

mopwithinmonth1month2month3month4month5month6month>6 and <=12month
3/1/20121035638012020039019987
4/1/201235638012020039063819349
5/1/20126380120200390638125018099
6/1/2012801202003906381250259915500
7/1/201212020039063812502599320012300
8/1/201220039063812502599320043207980
9/1/2012390638125025993200432079800
10/1/20126381250259932004320798000
11/1/201212502599320043207980000
12/1/201225993200432079800000
1/1/201332004320798000000
2/1/201343207980000000
3/1/201379800000000

Could you please advise me

Best,

Robert

1 Solution

Accepted Solutions
SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey

View solution in original post

22 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

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?

Regards,
Sergey
Not applicable
Author

i think crosstable(mop,order)

Not applicable
Author

Hi,

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

Best,

Robert

Not applicable
Author

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

sujeetsingh
Master III
Master III

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.

Not applicable
Author

Hi,

I am not aware the functions that you suggested me.

Please help me to do it in my sample application.

Best,

Robert

Eric_Tay
Employee
Employee

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).

sujeetsingh
Master III
Master III

Not applicable
Author

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