topic Re: Searching Field Value in another field in Set analysis in QlikView

Searching Field Value in another field in Set analysis

sarfaraz_sheikh — Mon, 10 Dec 2018 07:57:25 GMT

Hi Folks,

I need help regarding below issue.

I have below view and i want to print the following:

i> "ITRC\Remedy\1||1234" in "Try with Only Function" Expression when "StarAsset" columns has 1234 value

ii> "ITRC\Remedy\1||3456" in "Try with Only Function" Expression when "StarAsset" columns has 3456 value.

I have used the Only function to achieve this. and it is working correctly but based on selection.

When i select *1234* value from StartAsset column output will be like below as per expectation and Similarly for *3456* i am getting ITRC\Remedy\1||3456 in "Try with only function column". This works only if you select any value from "StarAsset " column othetwise it appears blank. I want it to be appear without selection.

I am getting ITRC\Remedy\1||1234, ITRC\Remedy\1||3456 = Concat([Treatment Asset],',')

I have tried with below Only Expression:

"Try with Only Function" = Concat({< [Finding Treatment Asset]={$(=vFinalValuesToLook)},[Treatment Asset]={"$(=Only({< [Finding Treatment Asset]={$(=vFinalValuesToLook)} >} StarAsset))"} >} [Treatment Asset],',')

Can anybody please help me how to search Field values in another field in set analysis. qvw is also attached here for your reference.

Thanks

Sarfaraz

Re: Searching Field Value in another field in Set analysis

Anil_Babu_Samineni — Mon, 10 Dec 2018 09:31:58 GMT

May be this?

=Concat({<StarAsset = {'*3456*'}, [Treatment ID] = {$(=Concat(Chr(39) & [Treatment ID] & Chr(39), ','))}>} [Treatment Asset], ',')

Re: Searching Field Value in another field in Set analysis

sunny_talwar — Mon, 10 Dec 2018 12:51:30 GMT

I don't think set analysis will work here... may be try with aggr and if statement like this

Concat(Aggr(If(WildMatch([Treatment Asset], StarAsset), [Treatment Asset]), StarAsset, StarFindingID, [Treatment Test], [Treatment Asset]))

Re: Searching Field Value in another field in Set analysis

sarfaraz_sheikh — Mon, 10 Dec 2018 14:54:39 GMT

Thank you very much stalwar ...It works as expected ....However i was wondering to get it done in set analysis itself because data is huge and will take a time to process it with AGGR function at front end.

Let me try to accomodate solution with real time data and will check it performance.

Thanks a lot once again. Really appreciate your help.

Thanks

Sarfaraz

Re: Searching Field Value in another field in Set analysis

sunny_talwar — Mon, 10 Dec 2018 14:58:33 GMT

The only way to make this efficient is to create a flag in the script

LOAD ...,
 If(WildMatch([Treatment Asset], StarAsset), 1, 0) as Flag
FROM ...;

Now you have this flag which you can use in your expression like this

Concat({<Flag = {1}>} [Treatment Asset])

or

Concat(DISTINCT {<Flag = {1}>} [Treatment Asset])

Re: Searching Field Value in another field in Set analysis

sarfaraz_sheikh — Mon, 10 Dec 2018 15:15:13 GMT

Correct But [Treatment Asset] and StarAsset coming from different table . and there is no link between that table.

Both tables are data island.

Moreover, it will work on selection basis only ..If i select Remedy1 then associated TreatmentAsset items will get compare with asset from another table . It will have to work based on user selection.

It would be great help if that can be done in backend as you said.

Re: Searching Field Value in another field in Set analysis

sarfaraz_sheikh — Mon, 10 Dec 2018 15:16:40 GMT

Correct But "TreatmentAsset" and StarAsset coming from different table . and there is no link between that table.

Both tables are data island.

It would be great help if that can be done in backend as you said.

Re: Searching Field Value in another field in Set analysis

sunny_talwar — Mon, 10 Dec 2018 15:37:49 GMT

In that case Aggr() with if statement is your only option.