topic Re: Search and restrict Straight table in QlikView

Search and restrict Straight table

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

Hi,

I have the following data format in a straight table where all fields are either dimensions or calculated dimensions,

ID	Name1	Name2	Tier
10	Chicago	NYC	White
10	Chicago	Chicago	Yellow
10	Chicago	Boston	Silver
10	Chicago	Seattle	Brown

In the above table ID field and Name1 remain same for an ID selection, in this case being 10. Only the ID is selected and hence the table gets populated based on ID selection. The tier information is based on the corresponding Name2 value.

My question is for a given selection of ID(which has many values) i will need to restrict the tier information as follows,

Will need to find the tier for Name2 which is equal to the Name1 field for the current ID selection- for this ID selection 10 the tier where Name1=Name2(Chicago) is Yellow
Based on the tier selected above i will have to restrict the column Tier based on the following priority Yellow>Silver>Brown>White and only show information greater than or equal to the current tier.

I am kind of stuck on this solution and would appreciate any help.

Thanks,

Santosh

Re: Search and restrict Straight table

vishsaggi — Tue, 21 Nov 2017 18:30:37 GMT

Can you explain your 2nd requirement not pretty clear sorry?

May be use calculated dimension for you first question Name2.

= Aggr(IF(Name1 = Name2, Name2), Name1)

Or as and Expression

= IF(Name1 = Name2, Name2) something like this.

Re: Search and restrict Straight table

sunny_talwar — Tue, 21 Nov 2017 18:34:56 GMT

So here you would want to see only Yellow?, but if for instance Tier was Silver, you would have wanted to see Silver and Yellow?

Re: Search and restrict Straight table

sgarim41 — Tue, 21 Nov 2017 18:36:52 GMT

Apologies if that wasn't clear. What i meant was, based on the tier selected from the 1st condition in this example Yellow, i will need to show only those Name2 which have tiers equal to or lesser in terms of priority as follows,

Yellow>Silver>Brown>White

So in this case i will be showing all the tiers since Yellow has highest priority, but there might be some scenarios where brown might be selected wherein only Brown or white needs to eb shown. So this needs to be dynamic. Hope this helps

Re: Search and restrict Straight table

sgarim41 — Tue, 21 Nov 2017 18:39:14 GMT

If its yellow, i will need to show all values lesser than or equal to in priority, so in this case Yellow, Silver, Bronze, White and in the case where the tier is Silver, should only show SIlver Bronze and N/A values.

Re: Search and restrict Straight table

sunny_talwar — Tue, 21 Nov 2017 19:15:22 GMT

And you will ever be looking at one ID at a time? Your users will never want to see more than one ID at any given point of time?

Re: Search and restrict Straight table

sgarim41 — Tue, 21 Nov 2017 19:42:34 GMT

Only one ID is displayed in the table at all times at a time which is based on a user search criteria. The Name1 values are based on the ID and hence get populated accordingly, but the Name2 values will change based on the selected ID.

Re: Search and restrict Straight table

sunny_talwar — Tue, 21 Nov 2017 19:51:51 GMT

May be like this

vVar

=If(Name1 = Name2, Tier)

and then this

If($(vVar) = 'Yellow', Tier,

If($(vVar) = 'Silver', If(Match(Tier, 'Silver', 'Brown', 'White'), Tier),

If($(vVar) = 'Brown', If(Match(Tier, 'Brown', 'White'), Tier),

If($(vVar) = 'White', If(Match(Tier, 'White'), Tier)))))

Re: Search and restrict Straight table

sgarim41 — Tue, 21 Nov 2017 21:43:03 GMT

Thanks Sunny for your suggestion. I was able to create the solution with your help. For the first part i created 2 variables 1 for holding the Name1 field value based on selected ID and another vOppName to hold the value of the Tier for the correspoding Name1 field as follows,

vOppName= =Maxstring({<Name2 = {'$(vName)'}>}Tier)

Once this was done it was just limiting the values based on priority like you have mentioned. But only difference, i used the following syntax

If(vVar = 'Yellow', Tier,

If(vVar = 'Silver', If(Match(Tier, 'Silver', 'Brown', 'White'), Tier),

If(vVar = 'Brown', If(Match(Tier, 'Brown', 'White'), Tier),

If(vVar = 'White', If(Match(Tier, 'White'), Tier)))))

Not sure why the $(vVar) which i usually use for variables did not work. Anyhow thanks for your help appreciate it

Re: Search and restrict Straight table

sunny_talwar — Wed, 22 Nov 2017 13:44:47 GMT

Very nice, I think the $(vVar) would have worked, if the variable was defined without a equal sign. I always get confused with the variables and tend not to use them. But, I am glad you were able to figure it out on your own.

Best,

Sunny