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

Scripting Problem

Hi everyone,

      I have Cumulative Sum field.I need last Value which is less than 1 lakh.After 1 lakh ,the data should be

(1 lakh)-Cumulativesum.Pls help me

Any suggestions would be greatly Appreciated.Little urgent.

Sample data and Application .

PFA,

Regards,

Divya

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one possible solution could be:

QlikCommunity_Thread_134258_Pic1.JPG.jpg

table1:

LOAD Name,

    Date,

    Year,

    Sales,

    CumulativeSum,

    Commission

FROM [http://community.qlik.com/servlet/JiveServlet/download/614780-127809/Requirement.xls] (biff, embedded labels, table is Sheet1$);

Left Join (table1)

LOAD *,

    If(Name=Previous(Name) and CumulativeSum<=100000 and Previous(CumulativeSum)>100000, 100000, Mod(CumulativeSum,100000)) as NewCumulativeSum

Resident table1

Order By Name, Date desc;

hope this helps

regards

Marco

View solution in original post

8 Replies
Not applicable
Author

hi ,

  I am using  Below Script

Tab3:

Load *,if(CumulativeSum<10000 ,Peek('CumulativeSum',0,'Tab1'))as New_C_sum Resident Tab1;

I am getting last peek value Which is lessthan 1 lakh .I want to replace that value with 1 lakh .Pls suggest me

Regards,

Divya

MarcoWedel

Hi,

one possible solution could be:

QlikCommunity_Thread_134258_Pic1.JPG.jpg

table1:

LOAD Name,

    Date,

    Year,

    Sales,

    CumulativeSum,

    Commission

FROM [http://community.qlik.com/servlet/JiveServlet/download/614780-127809/Requirement.xls] (biff, embedded labels, table is Sheet1$);

Left Join (table1)

LOAD *,

    If(Name=Previous(Name) and CumulativeSum<=100000 and Previous(CumulativeSum)>100000, 100000, Mod(CumulativeSum,100000)) as NewCumulativeSum

Resident table1

Order By Name, Date desc;

hope this helps

regards

Marco

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this script

Temp:

LOAD Name, 

    Date, 

    Year, 

    Sales, 

    CumulativeSum, 

    Commission ,

   If(Name = Previous(Name), Mod(CumulativeSum, 100000), CumulativeSum) AS NewCumulativeSum

FROM [http://community.qlik.com/servlet/JiveServlet/download/614780-127809/Requirement.xls] (biff, embedded labels, table is Sheet1$);

Regards,

Jagan.

Not applicable
Author

Hi Putturudivya,

Try this. This can replace the last peek value with 1 lakh.

Temp:

LOAD
     RowNo() AS ID,
     Name,
     Date,
     Year,
     Name&Year AS Name_Year,
     Sales,
     CumulativeSum,
     Commission
FROM
[Requirement.xls]
(
txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MapCumulativeSum:
MAPPING
LOAD
     ID,
     Peek(Sales,ID) AS Sales
Resident Temp;

MapNameYear:
MAPPING
LOAD
     ID,
     Peek(Name_Year,ID) AS Name_Year
Resident Temp;

NoConcatenate
Fact:
LOAD
 

     *,

     IF(Name_Year = ApplyMap('MapNameYear',ID) AND CumulativeSum <= 100000 AND CumulativeSum + ApplyMap('MapCumulativeSum',ID) > 100000, 100000, Mod(CumulativeSum,100000)) AS NewCumulativeSum

Resident Temp;

DROP Table Temp;

Linda

Not applicable
Author

Hi Macro,

Your Solution is Absolutely Correct.Thanks a lot .

I need one more Suggestion.the same way i need to do it for every 50k(for ex:50,000,100000,1,50,000....)

Sample data

PFA,

Regards,

Divya

MarcoWedel

Hi again,

QlikCommunity_Thread_134258_Pic2.JPG.jpg

table1:

LOAD Name,

     Date,

     Year,

     Sales,

     CumulativeSum,

     Commission

FROM [http://community.qlik.com/servlet/JiveServlet/download/615748-128101/Requirement3.xls] (biff, embedded labels, table is Sheet1$);

Left Join (table1)

LOAD *,

     If(Name=Previous(Name) and Div(CumulativeSum,50000)<Div(Previous(CumulativeSum),50000), Ceil(CumulativeSum/50000)*50000, Mod(CumulativeSum,50000)) as NewCumulativeSum

Resident table1

Order By Name, Date desc;

hope this helps

regards

Marco

Not applicable
Author

Hi Macro,

Thanks a lot.

MarcoWedel

you're welcome

regards

Marco