topic Re: Adjust below premium to above row and remove that row in QlikView

Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 14:04:45 GMT

@Hi,

I have a query which my customer have asked to manipulate for his better understanding.

In the above picture customer wants that BI/Rent Exp value should be adjusted or moved to one line above i.e. 96,000 should move above to replace 0 .

In short customer do not want the whole line of Index=NIL and rather want the values adjusted above for Index=B35.

Can anyone suggest how this can be achieved in Qlikview

Re: Adjust below premium to above row and remove that row

vishsaggi — Thu, 19 Jul 2018 14:06:19 GMT

We cannot see the image, can you share some sample data and expected output?

Re: Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 14:08:37 GMT

Can you see it now, I have reposted.

Re: Adjust below premium to above row and remove that row

ogautier62 — Thu, 19 Jul 2018 14:13:37 GMT

Hi,

add a dimension as :

if( index='NIL','B35',index)

and use sum() for your amount expression

regards

Re: Adjust below premium to above row and remove that row

vishsaggi — Thu, 19 Jul 2018 14:15:32 GMT

Is NIL a value coming from excel or is that going to be a Null value? Do you have this sample data file so that we can work on? May be you can try like

= IF(Above(BI/RentExposure,0,4) = 0, Below(BI/RentExposure),0)

Can you also show the expr used for BI/RentExposure?

Re: Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 14:25:44 GMT

Hi Olivier,

B35 is just one of the Index examples that I tried to show here.

Report have many scenarios where we have Indexes different than B35 for e.g. like N31 and NIL. So how to tackle that?

Re: Adjust below premium to above row and remove that row

ogautier62 — Thu, 19 Jul 2018 14:33:06 GMT

so I don't see if it depends on multiple codes,

another way : cumulate for all index, so 1 ligne for all scenarios

but you can't see the code

Re: Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 14:36:05 GMT

Hi Vishwarath,

NIL is a value coming from an Oracle table.

BI/Rent Exposure is a straight away a premium field coming from the sample table.

Re: Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 14:37:50 GMT

I guess I done think it will accumulate as Indexes are different

Re: Adjust below premium to above row and remove that row

vishsaggi — Thu, 19 Jul 2018 14:56:03 GMT

Can you share some sample data in an excel to look into?

Re: Adjust below premium to above row and remove that row

ogautier62 — Thu, 19 Jul 2018 14:58:24 GMT

if you supress index as expression,

and replace by concatenate(index,'/')

you'll have B35/NIL for example

Re: Adjust below premium to above row and remove that row

ogautier62 — Thu, 19 Jul 2018 15:16:47 GMT

may an awfull solution which could work :

as new dimension

if(BI/RentExp <> 0,index, aggr(MinString({<Index = {"=BI/RentExp = 0" >} index), you upper dimension or ID) )

ie : if BI/Rent <> 0 ligne is displayed,

= 0 it's cumulate with another line

best choice should be to do it in script

Re: Adjust below premium to above row and remove that row

kartikaysingh — Thu, 19 Jul 2018 15:30:07 GMT

So are you saying that I should take Item Index in expression and then disable it and then replace it with concatenate(Item Index,'/')?

Re: Adjust below premium to above row and remove that row

kartikaysingh — Fri, 20 Jul 2018 09:08:16 GMT

I did try this but for many columns I am getting - or blanks.

Re: Adjust below premium to above row and remove that row

ogautier62 — Fri, 20 Jul 2018 10:12:18 GMT

yes because you have a new dimension

expression must have an agregation function

are first text columns all same value for different index ?

if yes : you can use maxstring(your text column)

if no : in script, no choice

Re: Adjust below premium to above row and remove that row

kartikaysingh — Fri, 20 Jul 2018 10:47:14 GMT

I did try giving each dimension as maxstring(dimension) which did not worked. Will try it in script.

Thanks for your answers.

Re: Adjust below premium to above row and remove that row

Anil_Babu_Samineni — Fri, 20 Jul 2018 11:47:53 GMT

I Would like to test your application. Can we expect sample?

Re: Adjust below premium to above row and remove that row

kartikaysingh — Fri, 20 Jul 2018 12:04:30 GMT

I have attached the excel in the thread

Re: Adjust below premium to above row and remove that row

kartikaysingh — Fri, 20 Jul 2018 12:07:49 GMT

I have attached a sample excel.