topic Re: Variables Display Garbage After - QB Advanced Reporting in QlikView

Variables Display Garbage After - QB Advanced Reporting

Wed, 25 Nov 2020 16:16:04 GMT

‌When I go to Ctrl-Alt-V, almost all the installed variables have "Garbage after". I do not see any errors. Here is a photo:

Re: Variables Display Garbage After - QB Advanced Reporting

sunny_talwar — Sun, 01 Jan 2017 20:03:53 GMT

Why is your expression initiating with *,? I think if you replace *, with just =, the expression should ideally report no error

Re: Variables Display Garbage After - QB Advanced Reporting

Sun, 01 Jan 2017 22:01:00 GMT

It's looking better! But now I get Garbage after expression "as":

if( ([Classified Account Type] = 'Income' or [Classified Account Type] = 'Liability' or [Classified Account Type] = 'Equity') and [Debit Or Credit] = 'Credit',Amount,

if( ([Classified Account Type] = 'Asset' or [Classified Account Type] = 'Expense') and [Debit Or Credit] = 'Debit', Amount,

if( ([Classified Account Type] = 'Income' or [Classified Account Type] = 'Liability' or [Classified Account Type] = 'Equity') and [Debit Or Credit] = 'Debit',-Amount,

if(([Classified Account Type] = 'Asset' or [Classified Account Type] = 'Expense') and [Debit Or Credit] = 'Credit', -Amount)))) as [Amount With Sign], if( ([Classified Account Type] = 'Income' or [Classified Account Type] = 'Liability' or [Classified Account Type] = 'Equity') and [Debit Or Credit] = 'Credit',Quantity,

if( ([Classified Account Type] = 'Asset' or [Classified Account Type] = 'Expense') and [Debit Or Credit] = 'Debit', Quantity,

if( ([Classified Account Type] = 'Income' or [Classified Account Type] = 'Liability' or [Classified Account Type] = 'Equity') and [Debit Or Credit] = 'Debit',-Quantity,

if(([Classified Account Type] = 'Asset' or [Classified Account Type] = 'Expense') and [Debit Or Credit] = 'Credit', -Quantity)))) as [Quantity With Sign]

Re: Variables Display Garbage After - QB Advanced Reporting

marcus_sommer — Mon, 02 Jan 2017 12:07:15 GMT

In this window you specified only the variable-content without a name-alias and the variables gets those name which you had specified within the step before as you created the variable. Beside them it looked like a script-expression and not like a real variable ... here you will find many useful information about Variables.

- Marcus

Re: Variables Display Garbage After - QB Advanced Reporting

ramasaisaksoft — Mon, 02 Jan 2017 12:24:52 GMT

case 1:- can you place = symbol in front of IF condition

if we wrongly understood please copy paste the entire code so that we will help to you.

Re: Variables Display Garbage After - QB Advanced Reporting

Anonymous — Mon, 02 Jan 2017 12:45:10 GMT

I see "as" in the expression, I think maybe you copied the expression from a script for the dash, "as" is used in the script to define/rename a field, "as" can not be used in the expression in the "expression" tab in the chart properties. If you want to be used in the expression tab, then pls change the expression as below:

1. delete : as [Amount With Sign]

2. change : as [Quantity With Sign] to ")"

Zhihong

Re: Variables Display Garbage After - QB Advanced Reporting

Mon, 02 Jan 2017 13:06:31 GMT

I see you wrote "if( ([Classified Account Type] = 'Income' " in the top line and used it in other lines too.
I think you should delete one of the first two "(" and the space used between both brackets.

Hope this will work

Ivo

Re: Variables Display Garbage After - QB Advanced Reporting

Anonymous — Mon, 02 Jan 2017 13:17:58 GMT

Hi, Ivo,

I think the logical of the expression is correct, if see more of the if condition, it is:

if ( ( [Classified Account Type] = 'Income' OR condition1) AND condition2,

if(...),

if ( ( [Classified Account Type] = 'Income' OR condition3) AND condition4 , ..., ... )

So both of the 2 points you mentioned should not be the reasons that the expression fails.

Zhihong

Re: Variables Display Garbage After - QB Advanced Reporting

Mon, 02 Jan 2017 15:24:52 GMT

I am so close I can feel it!!! I cleaned it up a tad. I am so thankful for your assistance. This is the newest rendition:

Re: Variables Display Garbage After - QB Advanced Reporting

Anonymous — Mon, 02 Jan 2017 16:50:09 GMT

The red curly line basically means that the problem started, and it does not validate the rest expression. You can see only the first "if" is blue and all the rest "if" are not blue yet, which means they are not validated.

From the syntax error, it seems you do not have the field "Classified Account Type" , I guess that field name maybe comes from a re-name of some other field in the load script where you copied from. So you need to find what is exact is the field from the raw table, or you must rename is in your load script.

BTW, the 5th "if" should have the same indent as the 2nd "if", because the are 2 results from the 1st "if".

Zhihong

Re: Variables Display Garbage After - QB Advanced Reporting

Tue, 03 Jan 2017 04:30:02 GMT

!!!!! Zhihong !!!!! and everyone else!!!

Thank you so much!!!

if( ([Account.Account Type] = 'Income' or [Account.Account Type] = 'Other Current Liability' or [Account.Account Type] = 'Equity') and [Transactions.Debit Or Credit] = 'Credit',Transactions.Amount,

if( ( [Account.Account Type] = 'Assest' or [Account.Account Type] = 'Expense') and [Transactions.Debit Or Credit] = 'Debit', -Transactions.Amount,

if( ([Account.Account Type] = 'Income' or [Account.Account Type] = 'Other Current Liability' or [Account.Account Type] = 'Equity') and [Transactions.Debit Or Credit] = 'Debit',-Transactions.Amount,

if(([Account.Account Type] = 'Assest' or [Account.Account Type] = 'Expense') and [Transactions.Debit Or Credit] = 'Credit', -Transactions.Amount)))) - Transactions.Amount

Ok. Novice alert. I use Quickbooks and Advanced Reporting (through Qlik) is part of the package. In Advanced Reporting, Quickbooks already populates the variables and they make it clear not to touch them.

Mistake!!!

In the end, what I did was learn my data fields. To do this, I ran QlikView to generate every single field that my Quickbooks uses. I then painstakingly went through each of entries from the pre-installed variables and reentered the above variable. What a learning experience!!!

The moral of the story is for me to know my data!

The other take-away is to master the obvious first.

Lol

Thank you so much!!!

Re: Variables Display Garbage After - QB Advanced Reporting

engishfaque — Tue, 03 Jan 2017 04:42:42 GMT

Dear Jonathan,

It would be nice if you use Match function instead of writing multiple OR logical operators. After using Match function in your expression it will looks like smart expression. Match example is given below,

if(Match([Classified Account Type], 'Income', 'Liability', 'Equity')...

Kind regards,

Ishfaque Ahmed

Re: Variables Display Garbage After - QB Advanced Reporting

Tue, 03 Jan 2017 06:26:09 GMT

If you are using this expression in variable, then put this whole expression in single quote.

At the end u need this as a string not calculation.

Re: Variables Display Garbage After - QB Advanced Reporting

Anonymous — Tue, 03 Jan 2017 11:57:40 GMT

Yes, to using QV, the first and most important thing is to figure out your data and the data model after the load script.

And great that you figure this out.

Zhihong