Scripting Problem

[#1]

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

[#2]

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 · Accepted solution]

Hi,

one possible solution could be:

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]

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.

[#5]

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

[#6]

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,

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

[#8]

Hi Macro,

Thanks a lot.

[MarcoWedel]

you're welcome

regards

Marco