topic Re: Scripting Problem in QlikView

Scripting Problem

Sun, 21 Sep 2014 17:17:26 GMT

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

Re: Scripting Problem

Sun, 21 Sep 2014 18:15:51 GMT

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

Re: Scripting Problem

MarcoWedel — Sun, 21 Sep 2014 20:53:17 GMT

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

Re: Scripting Problem

jagan — Mon, 22 Sep 2014 03:12:18 GMT

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.

Re: Scripting Problem

Mon, 22 Sep 2014 07:26:20 GMT

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

Re: Scripting Problem

Mon, 22 Sep 2014 16:47:37 GMT

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

Re: Re: Scripting Problem

MarcoWedel — Wed, 24 Sep 2014 11:00:17 GMT

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

Re: Re: Scripting Problem

Wed, 24 Sep 2014 13:33:22 GMT

Hi Macro,

Thanks a lot.

Re: Scripting Problem

MarcoWedel — Wed, 24 Sep 2014 15:36:52 GMT

you're welcome

regards

Marco