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

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

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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:

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

marcelviegas — Tue, 03 Jul 2018 16:42:04 GMT

can you attach the qvw?

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

vishsaggi — Tue, 03 Jul 2018 16:53:17 GMT

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

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;

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

Anonymous — Wed, 04 Jul 2018 09:28:56 GMT

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?

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

vishsaggi — Wed, 04 Jul 2018 19:06:42 GMT

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

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

Anonymous — Thu, 05 Jul 2018 14:48:47 GMT

Please find attached requested vishsaggi‌

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

vishsaggi — Thu, 05 Jul 2018 20:04:42 GMT

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.

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

Anonymous — Fri, 06 Jul 2018 07:02:05 GMT

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.

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

vishsaggi — Fri, 06 Jul 2018 17:43:08 GMT

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

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

Anonymous — Tue, 10 Jul 2018 07:24:15 GMT

marcelviegas‌vishsaggi‌ you good today?

Any Luck?

Would appreciate.

Thanks

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

marcelviegas — Tue, 10 Jul 2018 12:22:17 GMT

I believe that the correct logic is Vishwarath Nagaraju made the 3rd comment.

try to study this method:

table_in_use:

load File,All

resident table_in_use

left join

LOAD File,

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

Resident table_in_use

Group By File

noconcatenate

final_table:

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

Resident table_in_use;

drop table table_in_use;

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

Anonymous — Tue, 10 Jul 2018 13:08:05 GMT

Thanks Marcel,

Would you mind downloading the qvf I attached previously and incorporate the code as you have shown.

I think the quickest way for me to grasp this is to see the finished result and learn from there/make further amendments to my dimensions.

thanks

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

marcelviegas — Tue, 10 Jul 2018 14:21:02 GMT

Sorry, I can not help you, I'm without qlik_sense installed on my computer, only qlikview.
if you want to send me the script in txt I set it for you.

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

vishsaggi — Tue, 10 Jul 2018 18:44:36 GMT

Will look into this in the evening.

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

Anonymous — Wed, 11 Jul 2018 06:50:10 GMT

vishsaggi‌marcelviegas‌ thanks for the effort you guys are putting in to assist me

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

vishsaggi — Thu, 12 Jul 2018 03:33:22 GMT

So sorry, completely got skipped to look into this. Can you remind me tomorrow please.

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

Anonymous — Fri, 13 Jul 2018 09:03:18 GMT

vishsaggi‌marcelviegas‌ Thanks guys, played around abit more with the script you guys sent and I managed.

Thanks, keep well