Next & previous syntax for NumberOrder

[#1]

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]

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]

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.

[#4]

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]

Hi,

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

Celambarasan

[jagan]

Hi,

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

Regards,

Jagan.

[rahulgupta]

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

[#8]

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]

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

[#10]

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