topic Re: Variables from Excel in QlikView

Variables from Excel

antonaks — Fri, 10 Nov 2017 13:28:19 GMT

I ask for help in working with variables.

I load variables from an Excel file.
For example, vFact and vPlan. After I begin to conduct mathematical operations on them, the result behaves quite strangely.

For example, the expression =$(vFact) / $(vPlan) displays a huge number, although if you take the value of each variable separately, everything is fine.

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 13:30:10 GMT

Try to explain your input data of excel with variables? And what you are getting then what you needed?

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 13:37:23 GMT

I'm using a download from a table with variables.

[Temp_Expressions]:

Replace only

LOAD

ID_Expression,

Name,

Expression,

Comment,

Feature

FROM

(ooxml, embedded labels, table is EXPRESSIONS);

FOR i = 0 to NoOfRows('Temp_Expressions') - 1

LET vName = Peek('Name', i, 'Temp_Expressions');

LET vComment = Peek('Comment', i, 'Temp_Expressions');

LET vFeature = Peek('Feature', i, 'Temp_Expressions');

LET $(vName) = Peek('Expression', i, 'Temp_Expressions') & '// $(vFeature):' & ' $(vComment)' ;

NEXT i

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 13:39:27 GMT

Sample Source

ID_Expression	Name	Expression
1	CountTT_CM_TD	Count(DISTINCT {$<CM_TD={1}>} [ID ФИЛИАЛА])
2	FactSales_CM_TD_Exec	Sum({$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}([СУММА С НДС]*[ТИП ОПЕРАЦИИ]))
3	FactChecks_CM_TD_Exec	Count(DISTINCT{$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}DOC_FILIAL_KEY)
4	FactQuantity_CM_TD_Exec	Sum({$<[НАЗВАНИЕ КАНАЛА]-={'Производитель'},[ID ТИПА ДОКУМЕНТА]={2,7,10}, [ВИД КОКУМЕНТА]={1,2,4,15,19,20,27,28,29,30}, CM_TD={1}>}([КОЛ-ВО]*[ТИП ОПЕРАЦИИ]))

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 13:45:46 GMT

When you say =$(vFact) / $(vPlan) receive huge number. I am expecting try to split into 2 variables of 2 text boxes like 1) $(vFact)

2) $(vPlan)

Then let us know the values you are getting and then tell us the result you are expecting?

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 13:54:29 GMT

Separately, each

$(vFact) = 85 345 706

$(vPlan) = 85 377 077

Division

$(vFact) / $(vPlan) = 85 342 240,17

Must be

$(vFact) / $(vPlan) =0,9963 ...

Re: Variables from Excel

biirka — Fri, 10 Nov 2017 14:13:15 GMT

Is your vFact formula of the form A - B?

If yes that would explain the issue you are having as you would get A - B / vPlan.

Try writing the formula as ($(vFact)) / ($(vPlan)).

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 14:15:06 GMT

What are you getting when you use vFact / vPlan ??

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 14:16:19 GMT

I wrote above:

$(vFact) / $(vPlan) = 85 342 240,17

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 14:20:24 GMT

I wrote above:

$(vFact) / $(vPlan) = 85 342 240,17

But, My reply is

What are you getting when you use vFact / vPlan ??

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 14:22:55 GMT

Sorry.

If vFact / vPlan I get NULL.

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 14:28:04 GMT

Actually I've tested in my end and it's working as expected. Can you share QVW to investigate.

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 14:52:22 GMT

Unfortunately, I can not send the current qvw.
But I managed to solve the problem.

If you put the variables and the operator one by one with a new line, then everything works.

Like this:

$(FactSales_CM_TD_Exec)

$(PlanSales_CM_TD_Exec)

Kind of weird...

Re: Variables from Excel

Anil_Babu_Samineni — Fri, 10 Nov 2017 15:34:37 GMT

I doubt, How function works in engine(QIX) level. Let me share some inputs

$(FactSales_CM_TD_Exec) = Sum(Sales)/10

PlanSales_CM_TD_Exec = Sum(Sales1) / 20

When we are doing

$(FactSales_CM_TD_Exec) / $(PlanSales_CM_TD_Exec)

Sum(Sales)/10 / Sum(Sales1)/10 // Here, engine works as alternative of division to each

($(FactSales_CM_TD_Exec)) / ($(PlanSales_CM_TD_Exec))

(Sum(Sales)/10) / (Sum(Sales1)/10) // Here, engine works as expected because of parenthesis we are division

Does this make sense, I am assuming same thing in your file. If you share some related information at least we will try our best.

Note - If you happy with the answer, Please mark your response as correct answer and other helpful where you fell help.

Re: Variables from Excel

antonaks — Fri, 10 Nov 2017 21:19:21 GMT

By trial and error, I came to this result.

I do not understand why, but if you write each variable in a new line, it works as it should.

// Not work

$(FactSales_CM_TD_Exec) / $(PlanSales_CM_TD_Exec)

// Good work

$(FactSales_CM_TD_Exec)

$(PlanSales_CM_TD_Exec)

// If use braces, they must be write in new line too

// Good work (if use braces)

(

$(FactSales_CM_TD_Exec)

$(FactChecks_CM_TD_Exec)

)

(

$(PlanSales_CM_TD_Exec)

$(PlanChecks_CM_TD_Exec)

)

Re: Variables from Excel

Anil_Babu_Samineni — Mon, 13 Nov 2017 13:26:19 GMT

Just to know where you are trying this? In fact, If you share some sample load variable expression to qlikview. Then that would be great. I am sorry May be i am not helps you this time

Re: Variables from Excel

antonaks — Mon, 13 Nov 2017 14:44:45 GMT

I'll give you an example, just a little later. Now very busy. So please forgive me.

Re: Variables from Excel

antonaks — Mon, 13 Nov 2017 16:19:47 GMT

I put an example. If you have questions please ask