How to set condition based on unique values using 2 lookup columns?

[Anonymous]

Good Day,

I please need assistance,

I wish to return either the Value 'R', or 'NR'

The File column has many unique values, and is accompanied by ALL with only 2 possible values.

To paint the picture, I want the dimension to tell me if the whole file is 'R' for ready, or 'NR' for not ready.

If all fields are populated with 'R', then the file is ready, but if just 1 'NR' is displayed on that specific file, then it must populate all fields to 'NR'.

***Remember, this must be done on a file level, so the fx must perform it's task per file and not on the entire column.***

Screenshot:

[marcelviegas]

can you attach the qvw?

[vishsaggi · Accepted solution]

Not sure if this is what you are looking for just using your own data modify your script like below:

File:

LOAD * INLINE [

File, ALL

CSE571, R

CSE571, NR

CSE569, R

CSE869, R

CSE869, NR

CSE869, NR

CSE1806, R

];

Left Join(File)

Final:

LOAD File,

     Count(IF(MATCH(ALL,'NR'),1,0)) AS NewAll

Resident File

Group By File;

NoConcatenate

Final:

LOAD File, NewAll, IF(NewAll > 1, 'NR', ALL) As ALL

Resident File;

Drop Table File;

[Anonymous]

Hi Vishwarath,

Thank you, apologies, you may have to be patient with me..

• I am working on Qlik sense, but the scripts are the same, no?
• My sql table has over 400,000 rows (file number(s) breakdowns)
• I have not used a load inline table before, the data entered is manual for it? is the following not automatically generated?
  

  "CSE571, R

  CSE571, NR

  CSE569, R

  CSE869, R

  CSE869, NR

  CSE869, NR

  CSE1806, R"

  The correct name for 'File' Column is 'FILE_REF'

  Thank you,

  If there no way for me to directly attach the qvf to this forum?

[vishsaggi]

‌No problem. So I used inline based on the data provided. Share the actual script with original fields.

[Anonymous]

Please find attached requested vishsaggi‌

[vishsaggi]

OK i have the script, so here FILE_REF is the correct field so what is the field name for ALL in your SQL SELECT script is it FILE_TYPE or REFERENCE. I mean where you have the Values R and NR, I need the field name.

[Anonymous]

Hi,

So the ALL column is actually I dimension I added to the sheet.

It has it's own script and does not form part of the sql table's raw data,

You are welcome to rename it to something else if that makes it easier.

Trust this clarifies.

[vishsaggi]

Ok. I will look into it later in the evening.

[Anonymous]

marcelviegas‌vishsaggi‌ you good today?

Any Luck?

Would appreciate.

Thanks