topic Re: How can I convert this IF Formula into a Variable in QlikView

How can I convert this IF Formula into a Variable

Tue, 30 Dec 2014 17:44:54 GMT

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!

Re: How can I convert this IF Formula into a Variable

Tue, 30 Dec 2014 21:29:00 GMT

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

Re: How can I convert this IF Formula into a Variable

NickHoff — Tue, 30 Dec 2014 22:00:05 GMT

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.

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 01:09:47 GMT

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

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 01:16:49 GMT

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

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 17:29:07 GMT

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

Re: How can I convert this IF Formula into a Variable

NickHoff — Wed, 31 Dec 2014 17:35:24 GMT

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.

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 19:14:52 GMT

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

];

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 19:28:07 GMT

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?

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 19:45:14 GMT

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.

Re: How can I convert this IF Formula into a Variable

Wed, 31 Dec 2014 20:03:28 GMT

Ok so for that expression to work all the metrics ( Doc Type,Function,QTY , ConvFactor ) should be in the same table. If Item% has a one-to-one relationship in the Inline table with Function and ConvFactor, then you can left join just those two fields into the Link Table and use that expression in the Link table through another resident Load.

Like -

LinkTable:

Load *

Resident LinkTable;

Left Join

Load Item%,

Function,

ConvFactor

Resident Inline Table;

Load *, Expression

Resident LinkTable;

Re: How can I convert this IF Formula into a Variable

Fri, 02 Jan 2015 19:03:04 GMT

Hi Ajay, Happy New Year!

Thanks again for your help, since I am fairly new to QV I am not able to apply what you have recommend me.

Do you have any info that I can read to understand better? and try to apply your concepts

Regards

Re: How can I convert this IF Formula into a Variable

Fri, 02 Jan 2015 19:23:04 GMT

There is this amazing book called 'Qlikview 11 for Developers' which has all the basic things needed to understand Qlikview with good examples. I would recommend going through that.

Happy New Year!!

Re: How can I convert this IF Formula into a Variable

Fri, 02 Jan 2015 21:02:21 GMT

Thank you for the advice, I will get it, but in the meantime do you think you can help me out with my app?