About $ Replacement Limitation

[atsushi_saijo]

Dear Gurus, I wish possible workaround on an issue of $ replacement. To make the logic change easy, I have made all the replacement in the initial loading script with SET statement. Single change in the business logic, many graph/grids changed simultaneously.

replacement without input values works. However; if the replacement statement included the input variable, the initialised value seems resident. (See below)

SET CONDITION= [GoodsReceivingDate] <= MonthEnd(Today(0),-$(X));

In this case, X is input value, initialised as '1'. Users may input '5' to have month shift of 5 months.

When loading script run, then '1' is initialised. However, no matter user change X value, the $(CONDITION) remains fixed. *$(=CONDITION) has same result.

Should we refrain using input value in the text replacement?

[Background not using Set Analyses for month field]

I refrained from using Set Analyses due to the report containing various different dates. Order Date, Purchase Date, Goods Receive Date (101), Goods Issue Date (601), .... in fact SAP situation, wname movement type is abundant, I rely on month-shift to combine all of them into the report.

I would appreciate for your support in advance.

[#2]

Atsushi,

Perhaps due to the $ that is interpreted in the script.

Use Chr(36) to replace the $ sign that would be interpreted by the script.

Set Condition = ' xxxxxx' & chr(36) & '(X)' ...

Fabrice

[atsushi_saijo]

I appreciate for fast answer. I attempted, however; the result was not affirmative.

I tried various combination ", ', and also just using CHR(36) in a plain SET statement.

"....."&CHR(36)&'(X)..........'

resulted somehow in 0. Replacing with the plane expression works. But it is interesting to learn CHR(36) as a way to replace characters.

[#4 · Accepted solution]

Atsushi,

SET X =1;
SET CONDITION= [GoodsReceivingDate] <= MonthEnd(Today(0),-$(X));
LET CONDITION2= '[GoodsReceivingDate] <= MonthEnd(Today(0),-' & chr(36) & '(X))';

gives me in variable overview:

Condition : [GoodsReceivingDate] <= MonthEnd(Today(0),-1)

Condition2 : [GoodsReceivingDate] <= MonthEnd(Today(0),-$(X))

So it should work (use LET)

Fabrice

[atsushi_saijo]

Very interesting to have your feedback: when I see in the variable overview in the original state, it is giving as '1' instead of $(X). Indeed the script inserts the value and automatically calculated it. Somehow LET statement gives straightaway 0 without any expression. I am delving into this matter and I will post a feedback soon.

[atsushi_saijo]

Succeeded, I appreciate for your support: with small question.

LET with CHR(36) works. Interactively works too.

Small question why originally did not work. I have had such a statement:

[Original]

SET CONDITION = 'MATCH([GoodsType],'A','B')=0 AND

          [GoodsReceivingDate] <= MonthEnd(Today(0)),-$(X))';

Now:

• SET is replaced as LET,
• $(X) is replaced as '.....' & CHR(36) & '(X) .....'.

This allows input field in tact in the charts and reporting grids.

But initially it did not work. But removing MATCH statement, the statement started working. Is there known rule that we must not use AND statement within LET statement?

***********************

P.S. I edited and broken down to fine pieces for the replacement. Currently refactoring on-going but $ with input field needs being careful. I appreciated for all your support.

[#7]

Atsushi,

No, I do not know well the differences.

I just know that SET should store the result of the formula, and LET the string (for later interpretation)

If it is a string, SET and LET should be the same. Except perhaps the $ inside the formula.

See the excellent answer by Jonathan Dienst about the local variables: http://community.qlik.com/thread/64743

Fabrice