Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

jadepepler
New Contributor

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
Esteemed Contributor III

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

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;

16 Replies
marcelviegas
Contributor II

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

can you attach the qvw?

vishsaggi
Esteemed Contributor III

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

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;

jadepepler
New Contributor

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

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
Esteemed Contributor III

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

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

jadepepler
New Contributor

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

Please find attached requested vishsaggi

vishsaggi
Esteemed Contributor III

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

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.

jadepepler
New Contributor

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

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
Esteemed Contributor III

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

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

jadepepler
New Contributor

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

marcelviegasvishsaggi‌ you good today?

Any Luck?

Would appreciate.

Thanks

Community Browser