Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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:

File R or NR.png

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

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;

View solution in original post

16 Replies
marcelviegas
Creator II
Creator II

can you attach the qvw?

vishsaggi
Champion III
Champion III

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
Not applicable
Author

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
Champion III
Champion III

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

Anonymous
Not applicable
Author

Please find attached requested vishsaggi

vishsaggi
Champion III
Champion III

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
Not applicable
Author

Hi,

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

ALL Dimension.png

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
Champion III
Champion III

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

Anonymous
Not applicable
Author

marcelviegasvishsaggi‌ you good today?

Any Luck?

Would appreciate.

Thanks