Re: Next & previous syntax for NumberOrder - Qlik Community

Report Inappropriate Content · ‎2012-03-05

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

CELAMBARASAN · ‎2012-03-05

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

jagan · ‎2012-03-05

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.

Report Inappropriate Content · ‎2012-03-06

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?

CELAMBARASAN · ‎2012-03-06

Hi,

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

Celambarasan

jagan · ‎2012-03-06

Hi,

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

Regards,

Jagan.

rahulgupta · ‎2012-03-06

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

Report Inappropriate Content · ‎2012-03-06

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

CELAMBARASAN · ‎2012-03-06

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

Report Inappropriate Content · ‎2012-03-06

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