topic Re: Need help in the script in QlikView

Need help in the script

Fri, 27 Sep 2013 09:24:34 GMT

Hello Friends,

As I tried to create a new column Progression with a existing table, as in the below and what I am expecting for instance, is for the customer 1 the NetSalesYear which should be compared with Year if the maximum of year is high then it should write as "UP" if its less than with the previous year then "Down" in the script I would like to write.

Please advise...

LIke 1 2004

Test:

LOAD * Inline [

CustomerID,Year,NetSalesByYear

1,2003, 1000

1,2004, 2000

2,2003, 500

2,2004, 200

3,2004, 10000

4,2003, 15000

4,2004, 1200

];

Regards

Chriss

Re: Need help in the script

jagan — Fri, 27 Sep 2013 09:44:41 GMT

Hi,

Try this

Temp:

LOAD

Inline

[

Test:

LOAD

(Previous(CustomerID) = CustomerID AND NetSalesByYear >= Previous(NetSalesByYear), 'UP',

(Previous(CustomerID) <> CustomerID, 'N/A', 'DOWN')) AS

Status

RESIDENT

Temp

ORDER

CustomerID

Year;

DROP

TABLE

Temp;

Regards,

Jagan.

Re: Need help in the script

DavidFoster1 — Fri, 27 Sep 2013 09:48:30 GMT

I think Jagan beat me to it:

Test_TMP:

LOAD * Inline [

CustomerID,Year,NetSalesByYear

1,2003, 1000

1,2004, 2000

2,2003, 500

2,2004, 200

3,2004, 10000

4,2003, 15000

4,2004, 1200

];

Test:

LOAD

CustomerID,

Year,

NetSalesByYear,

if(previous(CustomerID)<>CustomerID, '',

if(previous(NetSalesByYear) > NetSalesByYear, 'Down','Up')) AS Trend

RESIDENT Test_TMP;

DROP TABLE Test_TMP;

Re: Need help in the script

Fri, 27 Sep 2013 10:07:56 GMT

Hi both,

Jegan and David its working fine thanks for that.

I would like to know the working logic what you have applied could you please explain me to understand well.

Thanks ..

Chriss

Re: Need help in the script

DavidFoster1 — Fri, 27 Sep 2013 10:24:04 GMT

Jagans logic is more error proof than mine, but the principle is the same.

It relies on the data in Test_TMP being in the correct order (customer ascending then year ascending).

The 'previous' command allows you to look at the previous record in the source table (in this case test_TMP). The 2 levels of If statement are needed to handle the change in customerId before checking the previous year sales value.

As you can see there are many ways to express the IF statements but they are fundamentally the same.

I hope that helps.

Re: Need help in the script

jagan — Fri, 27 Sep 2013 10:32:42 GMT

Hi,

Using Previou() you will get the previous record value, we are comparing the previous record customer and current record customer if not equals then it is first record and we are marking it as 'N/A', if same then we are comparing the NetSalesByYear >= Previous(NetSalesByYear) if it satisfies then UP else DOWN.

Test_TMP:

LOAD * Inline [

CustomerID,Year,NetSalesByYear

1,2003, 1000

1,2004, 2000

2,2003, 500

2,2004, 200

3,2004, 10000

4,2003, 15000

4,2004, 1200

];

For example in the above for second record Previous(NetSalesByYear) will get 1000.

Hope this helps you.

Regards,

Jagan.

Re: Need help in the script

Fri, 27 Sep 2013 10:42:53 GMT

Awesome,

Thanks again both explaining me the things clearly and now understood the logic.

Thanks ad Regards

Chriss