How can I convert this IF Formula into a Variable

[#1]

I am struggling to create a variable of this IF statement:

=if([Doc Type]&Function='Credit MemoMultiply',sum(Quantity*ConvFactor)*-1,if(Function='Multiply',sum(Quantity * ConvFactor), if([Doc Type]&Function='Credit MemoDiv',sum(Quantity/ConvFactor)*-1,if(Function='Div',sum(Quantity / ConvFactor),if([Doc Type]='Credit Memo',Quantity*-1,Sum((Quantity)))))))

I want my app to run faster and also to use the variable to show the result by Responsible, write now I can only obtain the result by SKU, but as soon as I take out that dimension my result change.

Any thought.

Thank you!

[#2]

If you can do it in the back-end (script) then I would recommend going that route. That would be the most optimized approach. Maybe can you attach a sample file for review?

Thanks

[NickHoff]

You wouldn't be able to create one variable out of that nested if statement, but you could create a PICK.

PickClassification:

LOAD * INLINE [

ClassificationID, ClassificationDesc

1, 'CreditMemoMultiply'

2, 'Multiple'

3, 'DocType'

4, 'Div'

5, 'CreditMemo'

];

Then your expression would be:

PICK(ClassificationID,

sum(Quantity*ConvFactor)*-1,

sum(Quantity * ConvFactor),

sum(Quantity/ConvFactor)*-1,

sum(Quantity / ConvFactor),

Quantity*-1,Sum((Quantity))

From there you could create an Inline table with each PICK description, and then use a pick statement calling each statement on that Inline table.  As shown above.

[#4]

Ajay, thank you very much for your prompt reply.

I've tried to put together a new app, since the one that I am working on it is big.

The idea is that on the column Qty Conv I can get the following:

For Invoices:

Functions: (Multiply and Division)

a) Qty Conv= Qty * ConvFactor

b) Qty Conv= Qty / ConvFactor

c) Blank= The same amount in the QTY column

For Credit Memos:

Functions: (Multiply and Division)

a) Qty Conv= (Qty * ConvFactor)*-1

b) Qty Conv= (Qty / ConvFactor)*-1

c) Blank= The same amount in the QTY column

And a Chart by responsible with the Qty Conv

[#5]

Nick thank you very much for your help, I've tried to used the Pick approach that you recommended, nevertheless I am getting  NET 0, maybe I am doing something wrong....

I've attached the Inline Table in the Script and then created an expression

Here is the result

[#6]

Please look at the attached file. The calculation that you are doing on the Front-end, I just put in on the back-end and did a straight forward Sum(Exp) in the Straight table.

Thanks

[NickHoff]

It looks like you have 5 different statements in your pick, but only 4 in your inline.   The order they are displayed in the INLINE is the order they will be calculated in your PICK.

[#8]

Ajay it works smoothly and Perfect , thank you very much, but now when I am trying to included in my original application (with link and Inline tables) I am getting an error Field Not found <Doc Type>

Help pleaseeee

I am attaching part of my script:

[Link Table]:

// Load Distinct

LOAD Distinct

  %Key as [%TEMP Key Field],

  [Posting Date],

        [Doc Type],

          [Document No_],

     [Bill-to Customer No_],

     County,

     %Item,

          If([Doc Type]='Credit Memo',Amount*-1,Amount)     As Amount,

     If([Doc Type]='Credit Memo',Quantity*-1,Quantity)     As Quantity

       Resident [Hsales]

         Where Quantity <>0 ;

Concatenate ([Link Table])

  Load Distinct

     %Key,

  "Posting Date",

"Document No_",

    Type,

   %Item,

    "Unit of Measure",

     If([Doc Type]='Credit Memo',Quantity*-1,Quantity)     As Quantity,

         "Unit Price",

   If([Doc Type]='Credit Memo',Amount*-1,Amount)     As Amount, 

    "Bill-to Customer No_",

    [Doc Type],

    "Customer Price Group"

  Resident Sales

  Where Not Exists([%TEMP Key Field], [%Key]) and Quantity <>0 ;

Drop Field [%TEMP Key Field];

Drop Fields  "Posting Date",

"Document No_",

//    "Sell-to Customer No_",

    Type,

    %Item,

    "Unit of Measure",

    Quantity,

    "Unit Price",

    Amount,

    "Bill-to Customer No_",

    [Doc Type],

    "Customer Price Group"

     From [Sales];

Drop Fields [Posting Date],

     [Doc Type],

     [Document No_],

     [Bill-to Customer No_],

     County,

  %Item,

     Amount,

     Quantity

      From [Hsales];

UOM_SELECTION:

Load *,

if([Doc Type]&Function='Credit MemoMultiply',(QTY*ConvFactor),if(Function='Multiply',(QTY * ConvFactor), if([Doc Type]&Function='Credit MemoDiv',(QTY/ConvFactor),if(Function='Div',(QTY / ConvFactor),if([Doc Type]='Credit Memo',QTY,((QTY))))))) as Exp

INLINE [%Item, ItemConverted, ItemDescriptionConverted,Function, ConvFactor

I9006, I9011, DOLORES-00064 New Tuna in Oil 48/5oz/112cs, Multiply, 8

I9006, I9016, DOLORES-00065 New Tuna in Water 48/5oz/112cs, Multiply, 8

I9006, I9008, DOLORES-00071 New Tuna in Jalapeño 24/5oz/224cs, Multiply, 8

I9006, I9007, DOLORES-00068 New Tuna in Chipotle Sauce 24/5oz/224cs, Multiply, 8

I9006, I9014, DOLORES-00066 New Tuna inSalad with Vegetables 24/5oz/224cs, Multiply, 8

I9006, I9009, DOLORES-00069 New Tuna in Mexican Style 24/5oz/224cs, Multiply, 8

I9006, I9013, DOLORES-00067 New Tuna in Pickled Vegetable 24/5oz/224cs, Multiply, 8

I9012, I9011, DOLORES-00064 New Tuna in Oil 48/5oz/112cs, Multiply, 56

I9017, I9011, DOLORES-00064 New Tuna in Oil 48/5oz/112cs, Multiply, 40

I9017, I9016, DOLORES-00065 New Tuna in Water 48/5oz/112cs, Multiply, 16

I9023, I9027, DORADO-00057 Tuna in Water 24/5oz/224cs, Multiply, 32

I9023, I9026, DORADO-00055 Tuna in Oil 24/5oz/224cs, Multiply, 24

I9031, I9011, DOLORES-00064 New Tuna in Oil 48/5oz/112cs, Multiply, 24

I9031, I9016, DOLORES-00065 New Tuna in Water 48/5oz/112cs, Multiply, 16

I9031, I9008, DOLORES-00071 New Tuna in Jalapeño 24/5oz/224cs, Multiply, 5

I9031, I9007, DOLORES-00068 New Tuna in Chipotle Sauce 24/5oz/224cs, Multiply, 2

I9031, I9014, DOLORES-00066 New Tuna inSalad with Vegetables 24/5oz/224cs, Multiply, 5

I9031, I9009, DOLORES-00069 New Tuna in Mexican Style 24/5oz/224cs, Multiply, 4

I9031, I9013, DOLORES-00067 New Tuna in Pickled Vegetable 24/5oz/224cs, Multiply, 2

I9008, I9008, DOLORES-00071 New Tuna in Jalapeño 24/5oz/224cs, Div, 2

I9009, I9009, DOLORES-00069 New Tuna in Mexican Style 24/5oz/224cs, Div, 2

I9014, I9014, DOLORES-00066 New Tuna inSalad with Vegetables 24/5oz/224cs, Div, 2

I9007, I9007, DOLORES-00068 New Tuna in Chipotle Sauce 24/5oz/224cs, Div, 2

I9013, I9013, DOLORES-00067 New Tuna in Pickled Vegetable 24/5oz/224cs, Div, 2

I9010, I9010, DOLORES-00064(Kehe) New Tuna in Oil 24/5oz/224cs, Div, 2

I9015, I9015, DOLORES-00065(Kehe) New Tuna in Water 24/5oz/224cs, Div, 2

I9027, I9027., DORADO-00057 Tuna in Water 24/5oz/224cs, Div, 2

I9018, I9018, DOLORES-10033 Tuna in Pickled Vegetables 24/6oz/196cs, Div, 2

I9026, I9026, DORADO-00055 Tuna in Oil 24/5oz/224cs, Div, 2

I9025, I9025, DORADO-00081 New Tuna in Water with Soy 24/5oz/224cs, Div, 2

I9024, I9024, DORADO-00079 New Tuna in Oil with Soy 24/5oz/224cs, Div, 2

I9021, I9021, DOLORES-10029 Tuna in Jalapeño 24/6oz/196cs, Div, 2

I9022, I9022, DOLORES-10030 Tuna in Salad with Vegetables 24/6oz/196cs, Div, 2

I23036, I23036, TAJIN-00017 Fruit Seasoning 12/5.0oz (12 unit by case), Div, 2

I23047, I23047, TAJIN-90604 Shipper Fruit Seasoning 96pc, Multiply, 4

I23054, I23054, TAJIN-90604 WM Shipper Fruit Seasoning 96, Multiply, 4

I23064, I23038, TAJIN-00010-W Fruit Seasoning WM 24/5.0oz, Multiply, 3

I23064, I23039, TAJIN-00621 Fruit Seasoning Low Sodium 24/5.0oz, Multiply, 1

];

[#9]

The UOM_Selection table, is it loaded from Link Table ? Is the Link_Table and Inline Table concatenated ?

And you get the error while loading UOM_SELECTION table?

[#10]

UON is the inline table, I've created to obtain the info to convert the quantity.

Link Table and Inline are not concatenated. Here is the structure:

The error I get is the following:

Thank you very, very much.