Conditional Dimension vs Conditional Variable- boolean

[nina_k]

Hello,

I have created a dimension [UOM Mismatch] in a table which compares UOM from two different tables:

=if(UOM=[Vchr UOM],'No','Yes')

This works fine.

I wanted to use this in Set Analysis to count the distinct number of Purchase Orders for which this occu, therefore I created a variable with the same formula:

vUOM_Mismatch=if(UOM=[Vchr UOM],'No','Yes')

However, the variable is not working. I have tested dropping it in the table as a dimension  to compare the results, however it simply returns the formula in the column values "if(UOM=[Vchr UOM],'No','Yes')".

Is there a better way to go about this?

[bharathadde]

Try to use 

vUOM_Mismatch as $(vUOM_Mismatch) in expressions

[nina_k]

Thank you Bharathadde. That works for adding it as a dimension in the table . The results from the dimension as a variable matches the results from the dimension as a formula.

In building the set analysis, I have tried these. However it returns the total distinct count of [PO ID] and not just those where the UOM_Mismatch occurs:

=count({<$(vUOM_Mismatch)={'Yes'}>}distinct[PO ID]) ---->0

=count({$<$(vUOM_Mismatch)={'Yes'}>}distinct[PO ID]) ---->0

=count({$<vUOM_Mismatch={'Yes'}>}distinct[PO ID])   ----> 2.3k or the total of count(distinct([PO ID])

[bharathadde]

Try this

Create a column like below in the loading script

Load

UOM,

[Vchr UOM],

if(UOM=[Vchr UOM],'No','Yes') as Mismatch,

........

....... from your database;

Write expression as Below

 Count({<Mismatch={'Yes'}>}distinct [PO ID])

[nina_k]

Would my idea of using a variable's response not work?

I am loading 3 records from OLE DB connection (2 of which are concatenated, the last one is on a right join). Could you indicate where Qlik allows for me to create that formula knowing the two fields belong to different tables? I have tried at the end of everything and it hasn't worked so far but probably because I am using wrong syntax when building the new field?

[CONTRACTS2014-2015Q1]:
LOAD
`Base Price`,
Contract,
Currency,
`Default`,
`Last Dttm`,
`Line`,
Location,
`LT Days`,
UOM,
`User Modify`,
Version,
[Contract]&'-'&[Line]&'-'&[Version] AS [ContractID];
SQL SELECT
`Base Price`,
Contract,
Currency,
`Default`,
`Last Dttm`,
`Line`,
Location,
`LT Days`,
UOM,
`User Modify`,
Version,
[Contract]&'-'&[Line]&'-'&[Version] AS [ContractID]
FROM `CONTRACTS2014-2015Q1`;

LIB CONNECT TO 'ContractNew2.accdb';
Concatenate([CONTRACTS2014-2015Q1])
LOAD
`Base Price`,
Contract,
Currency,
`Default`,
`Last Dttm`,
`Line`,
Location,
`LT Days`,
UOM,
`User Modify`,
Version,
[Contract]&'-'&[Line]&'-'&[Version] AS [ContractID];
SQL SELECT
`Base Price`,
Contract,
Currency,
`Default`,
`Last Dttm`,
`Line`,
Location,
`LT Days`,
UOM,
`User Modify`,
Version,
[Contract]&'-'&[Line]&'-'&[Version] AS [ContractID]
FROM `CONTRACTPRICE2015Q2-2019Q2`;

LIB CONNECT TO 'MtchExcp2019.accdb';

RIGHT JOIN
[CLOSEDME]:
LOAD `Abs ME Extnd Price`,

.....,
`PO ID`,
....
`Vchr UOM`,

....
[Cntrct ID]&'-'&[Cntrct Ln]&'-'&[Cntrct Ver] AS [ContractID];
SQL SELECT `Abs ME Extnd Price`,

....,
`PO ID`,
....,
`Vchr UOM`,

....,

[Cntrct ID]&'-'&[Cntrct Ln]&'-'&[Cntrct Ver] AS [ContractID]
FROM MatchExceptionCLOSED2019;

[bharathadde · Accepted solution]

Create a new Table and drop old table

[CONTRACTS2014-2015Q1_New]:

Load

*,

if(UOM=[Vchr UOM],'No','Yes') as Mismatch

Resident [CONTRACTS2014-2015Q1];

Drop table [CONTRACTS2014-2015Q1];

[nina_k]

It looks like this worked.

I am not sure I understand why however it gets it done for now. Thank you!