topic Re: Next & previous syntax for NumberOrder in QlikView

Next & previous syntax for NumberOrder

Mon, 05 Mar 2012 11:17:22 GMT

Hello,

I'm new at Qlickview and hope anyone can help me with the following:

I have customerdata with customernumbers and ordernumbers and want to know the NumberOrder; meaning per customer if it's the first, second or third (or more) order.

I thought of using next and previous syntax (although i'm not sure what those look like), and tried the following (new formulas in bold):

Load

// "No_" as DocumentNo

"Document No_"&'-'&"No_" as DocuItemNo

, "Sell-to Customer No_"as CustomerNo

, "Package Tracking No_" as PackageTrackingNo

, "Promotion Name" as PromotionName

, "Promotion ID" as PromotionID

, "Discount Description" as DiscountDescription

, IF (NEXT(CustomerNo)=CustomerNo, CustomerNo,1) AS OrderCode

// so for customers who placed more orders, those orders will be coded with 1 and then we can calculate NumberOrder

, IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(OrderCode)+1,1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_"

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

But this is not working... Does anyone have a solution?

Regards,

Eline

Re: Next & previous syntax for NumberOrder

CELAMBARASAN — Mon, 05 Mar 2012 11:43:32 GMT

Hi,

Rename the "Sell-to Customer No_" field in the database side itself.

Check with this.

Load

// "No_" as DocumentNo

"Document No_"&'-'&"No_" as DocuItemNo

, CustomerNo

, "Package Tracking No_" as PackageTrackingNo

, "Promotion Name" as PromotionName

, "Promotion ID" as PromotionID

, "Discount Description" as DiscountDescription

// so for customers who placed more orders, those orders will be coded with 1 and then we can calculate NumberOrder

, IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder;\\IF (Peek(CustomerNo)=CustomerNo, Peek(NumberOrder)+1,1)

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

Celambarasan

Re: Next & previous syntax for NumberOrder

jagan — Mon, 05 Mar 2012 12:18:16 GMT

Hi,

There is no Next() in qlikview, we have Previous() using this we can arrive the Number order.

TableName:

Load

'Document No_'&'-'&'No_' as DocuItemNo

, CustomerNo

, 'Package Tracking No_' as PackageTrackingNo

, 'Promotion Name' as PromotionName

, 'Promotion ID' as PromotionID

, 'Discount Description' as DiscountDescription

, IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

Regards,

Jagan.

Next & previous syntax for NumberOrder

Tue, 06 Mar 2012 08:59:07 GMT

Thanks guys, but unfortunately it's not working yet... I get the following comment when running the script:

Error in expression: previous takes 1 parameter

Any idea what that means and how i can solve it?

Next & previous syntax for NumberOrder

CELAMBARASAN — Tue, 06 Mar 2012 09:00:40 GMT

Hi,

Can you post your script as it is which your using now?

Celambarasan

Next & previous syntax for NumberOrder

jagan — Tue, 06 Mar 2012 09:02:10 GMT

Hi,

Previous() has only parameter, did you give more than one? Attach the script you used.

Regards,

Jagan.

Next & previous syntax for NumberOrder

rahulgupta — Tue, 06 Mar 2012 09:48:07 GMT

Use This:

TableName:

Load

'Document No_'&'-'&'No_' as DocuItemNo

, CustomerNo

, 'Package Tracking No_' as PackageTrackingNo

, 'Promotion Name' as PromotionName

, 'Promotion ID' as PromotionID

, 'Discount Description' as DiscountDescription

, IF (Peek(CustomerNo)=CustomerNo, (Peek(NumberOrder)+1),1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

Regards

Rahul Gupta

Next & previous syntax for NumberOrder

Tue, 06 Mar 2012 12:59:45 GMT

Hi,

I don't know how to attatch a complete script, but right now it looks like this (following your suggestions):

CustomerDocument:

Load

// "No_" as DocumentNo

"Document No_"&'-'&"No_" as DocuItemNo

, CustomerNo

, "Package Tracking No_" as PackageTrackingNo

, "Promotion Name" as PromotionName

, "Promotion ID" as PromotionID

, "Discount Description" as DiscountDescription

, IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder+1,1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

and then i get the error: previous takes only one parameter. So I don't know what i've done wrong.

Thanks again for your help,

Eline

Next & previous syntax for NumberOrder

CELAMBARASAN — Tue, 06 Mar 2012 13:02:59 GMT

Hi,

You missed paranthesis PREVIOUS(NumberOrder)+1

Check with the below script.

CustomerDocument:

Load

// "No_" as DocumentNo

"Document No_"&'-'&"No_" as DocuItemNo

, CustomerNo

, "Package Tracking No_" as PackageTrackingNo

, "Promotion Name" as PromotionName

, "Promotion ID" as PromotionID

, "Discount Description" as DiscountDescription

, IF (PREVIOUS(CustomerNo)=CustomerNo, PREVIOUS(NumberOrder)+1,1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

Celambarasan

Next & previous syntax for NumberOrder

Tue, 06 Mar 2012 13:15:41 GMT

Thanks Celambarasan, i did not get the error this time.

But... i'm getting another one: cannot find field <NumberOrder>

This doesn't surprise me since NumberOrder is a field that I haven't defined before. And i tried to solve this by defining this before i use the previous function (see my first post) but then i found out that NEXT is not a function in Qlickview, so that obviously didn't work. Do you have another solution?

Regards,

Eline

Re: Next & previous syntax for NumberOrder

CELAMBARASAN — Tue, 06 Mar 2012 13:17:18 GMT

Hi,

Then try with peek function instead of Previous.

CustomerDocument:

Load

// "No_" as DocumentNo

"Document No_"&'-'&"No_" as DocuItemNo

, CustomerNo

, "Package Tracking No_" as PackageTrackingNo

, "Promotion Name" as PromotionName

, "Promotion ID" as PromotionID

, "Discount Description" as DiscountDescription

, IF (PEEK('CustomerNo')=CustomerNo, PEEK('NumberOrder')+1,1) as NumberOrder;

SQL SELECT

"Document No_"

, "Sell-to Customer No_" as CustomerNo

, "No_"

, "Package Tracking No_"

, "Promotion Name"

, "Promotion ID"

, "Discount Description"

FROM "$(DB_DATASOURCE)".dbo."$(DB_COMPANY)$Sales Invoice Line"

ORDER BY "Sell-to Customer No_";

Celambarasan

Edit:Added single quotes in the peek function. Message was edited by: Celambarasan Adhimulam