topic Requirement in Straight Table in QlikView

Requirement in Straight Table

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

Hi Community,

I have requirement like

Show Invest ID where not equal to (Country Jan and Country Dec) or (CCR Jan and CCR Dec)

For the above requirement

1. I have added Invest ID as Dimension and disabled in Presentation

2. I have added expression for Invest ID as if([Country Dec]<>[Country Jan] or [CCR Dec]<>[CCR Jan],INVEST_ID)

3. for Expression - Country Dec: Only({<%Month = {'Dec'}>}COUNTRY_NAME)

4. for Expression - Country Jan: Only({<%Month = {'Jan'}>}COUNTRY_NAME)

5. for Expression - CCR Dec: Only({<%Month = {'Dec'}>}CCR)

6. for Expression - CCR Jan: Only({<%Month = {'Jan'}>}CCR)

7. for Expression - Sale Jan: Sum({<%Month = {'Jan'}>}Sales)

8. for Expression - Sale Dec: Sum({<%Month = {'Dec'}>}Sales)

So Now I got straight table like below:

But users are looking out put only where we have values in Inv ID so they don't wanted to see rest of the data

For ex they wanted to see only highlited ones like below.

How can we achieve this requirement, can some one pls help me. Do I need to changes expressions or any other approach.

Re: Requirement in Straight Table

Chanty4u — Thu, 17 Mar 2016 10:33:57 GMT

in dimension tab

chk supress wen values is null?

Re: Requirement in Straight Table

Kushal_Chawda — Thu, 17 Mar 2016 10:38:35 GMT

try adding calculated dimension for Inv ID as

if(len(trim([Inv ID])<=0,null(),[Inv ID])

and check 'suppress when values is null'

Re: Requirement in Straight Table

Chanty4u — Thu, 17 Mar 2016 10:41:23 GMT

orelse u can create a field

Load

if (Fieldname <> 0,FieldName) as Fieldname

Re: Requirement in Straight Table

priyarane — Thu, 17 Mar 2016 10:58:50 GMT

All the suggestions are not working out...

First of all is my approach is correct

Re: Requirement in Straight Table

ThornOfCrowns — Thu, 17 Mar 2016 11:06:57 GMT

You are forcing the blank lines for INVEST_ID, which I presume is the Inv_ID column :

I have added expression for Invest ID as if([Country Dec]<>[Country Jan] or [CCR Dec]<>[CCR Jan],INVEST_ID)

I am also presuming that you don't have an INVEST_ID for every row.

Therefore, if you remove the above expression and then use your INVEST_ID dimension, you'll be able to supress the blank rows as per chanty4u‌ suggestion

Re: Requirement in Straight Table

tyagishaila — Fri, 18 Mar 2016 05:15:37 GMT

if issue is, just want to see data where Inv ID is available,

//But users are looking out put only where we have values in Inv ID so they don't wanted to see rest of the data//

than solution is

Go to Dimension Tab --> Check Suppress When Value is Null for Inv ID dimension.

Re: Requirement in Straight Table

Chanty4u — Fri, 18 Mar 2016 05:16:24 GMT

did u tried dis?

Re: Requirement in Straight Table

tyagishaila — Fri, 18 Mar 2016 05:26:12 GMT

what is your dimension in straight table.

as you describe straight table has expressions only.

share qvw file

Re: Requirement in Straight Table

jonathandienst — Fri, 18 Mar 2016 05:26:13 GMT

To suppress the row, all the expressions must valuate to null or zero. So you will need InvID as an expression that evaluates to null (NOT blank) when it has no value, and all the other expression will need to test this to return a zero if InvID is null. If you post a qvw with some representative data, then I can explain in more detail what you need to do, but the other expressions will be something like:

=If(IsNull(<... expression for invID ...>), 0, <... oriiginal expression ...>)

Re: Requirement in Straight Table

tyagishaila — Fri, 18 Mar 2016 06:16:35 GMT

you can try this

if( [Country Dec]<>[Country Jan] or [CCR Dec]<>[CCR Jan], INVEST_ID, 0)

add in all expressions except [Inv ID]

if( [Inv ID] = 0, 0, Only({<%Month = {'Jan'}>}COUNTRY_NAME) )

....

...

....

do it for all expressions.

hope it will work.