topic Re: Hiding certain rows in a straight table in QlikView

Hiding certain rows in a straight table

Qlik_ULG — Tue, 27 Feb 2018 14:27:09 GMT

Hi,

I have a straight table, that is built using 5 cross tables. They are linked using 1 PK, but it is not unique for each row as a result of coming from cross tables.

I have built a straight table, but it shows the rows as including NULL values, but also showing all available possible rows.

so to 'workaround' this, I suppress null values in the dimension limits setting, and the correct rows are then displayed, with unique values showing in the correct dimensions and rows.

Now as you can see here, there are 'double' rows for each of the Owner1, Owner2, Owner3 - this is incorrect. It should only be taking in the rows that have have a CAR Action Close Date. For Owner4, it is correct as that is actually null in the data.

Is there a way to only include certain rows - in this case it would be the first (or top) row.

Thanks in advance.

Re: Hiding certain rows in a straight table

Anonymous — Tue, 27 Feb 2018 15:40:56 GMT

Hi Shane,

It seem your primary requirement is to display all the rows that have CAR Action Close Date .

Even if Table C has null value for CAR Action Close Date but has matching CAR_CAR key this row should be displayed.

In case above is correct , then I suggest you to edit your load script and add a Inner Join on the Table C.

This will bring only rows where there is data (even null value) for CAR Action Close Date .

Hope This helps.

Thanks.

Re: Hiding certain rows in a straight table

isingh30 — Tue, 27 Feb 2018 16:55:00 GMT

Please share your data or app.

Thank you!

Re: Hiding certain rows in a straight table

Qlik_ULG — Tue, 27 Feb 2018 17:01:28 GMT

I cannot share the data, but it consists of ~80 columns, which I have converted into tables using cross tables.

here is my script.

Re: Hiding certain rows in a straight table

isingh30 — Tue, 27 Feb 2018 18:18:07 GMT

Try this in your table -

only(${<CAR Action close date = {'your dates'}>}Age)

Thank you!

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 09:50:40 GMT

Hi Abhijit,

Thanks for your reply.

Which table do you suggest joining it to?

When I try to do Inner Join Table C to Table A, I get an error - illegal combination of prefixes.

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 10:44:08 GMT

Thank you for your reply Ishtdeep,

I get a calculation error when using that expression. Should the $ sign be included?

I have tried and if(match) expression but also get errors then.

Re: Hiding certain rows in a straight table

Anonymous — Wed, 28 Feb 2018 12:03:41 GMT

Hi,

Before Performing Join operation, edit your cross table script and add a Load Statement to load the tables in separate QVD files.

After the QVD Files are ready then load them in your model. Perform the join operation while loading the QVDs.

You can do a Table C inner join Table D ; or Table C right outer join table D.

Re: Hiding certain rows in a straight table

isingh30 — Wed, 28 Feb 2018 14:49:41 GMT

$ sign is used for current selection in set analysis. You can remove that. I can do this for you but i need app or raw data for a better understanding.

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 15:01:48 GMT

Hi Ishteep,

please find attached the data & app.

Thanks,

Shane

Re: Hiding certain rows in a straight table

isingh30 — Wed, 28 Feb 2018 15:59:53 GMT

Try like this in your expression -

only({<ActionCloseDate = {'3/8/2017'}>}ActionOwner)

Thank you!

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 16:04:32 GMT

Hi Ishtdeep,

This again shows an error for me. Are you including this as an expression?

Also, I want to be able to see the date that is appropriate, not hard code in dates as there will be dates in the future. It should show the appropriate CAR CloseDate for the CAR Action ID, Owner & Date Initiated.

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 16:46:43 GMT

Hi Ishtdeep, when I include the following expression, the correct data is shown,

only({<ActionCloseDate = {'Date action closed - 1'},ActionOwner= {'Owner - 1'},ActionOpenDate= {'Date action initiated - 1'},ActionCloseDate= {'Date action closed - 1'}>}date(CloseDate))

But it only shows for the correct row - which is correct. I now have to include this for all rows, not just row 1.

Is there a way of including multiple ONLY statements in the one expression?

Thanks.

Re: Hiding certain rows in a straight table

isingh30 — Wed, 28 Feb 2018 16:52:14 GMT

Try - 'Owner *' . Let me know.

Thanks.

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 17:03:41 GMT

No, I still get a NULL value for all rows when I include 'Owner - *'

If I include, 'Owner - 1; or 'Owner -2' etc, the correct data shows in the correct row. I need to find a way of combining all these together,

Thanks.

Re: Hiding certain rows in a straight table

Qlik_ULG — Wed, 28 Feb 2018 17:19:19 GMT

Hi Abhijit,

Thanks for the reply,

I have saved to QVDs and loaded a new QVW.

When I perform this join, I still get the same error. The dates are not linked still. Am I performing the join incorrectly?

but the data does not show up. If I supress null values to get rid of the duplicates, it does not show the correct NULL values.

Re: Hiding certain rows in a straight table

isingh30 — Wed, 28 Feb 2018 17:28:33 GMT

Try this in expression - 'Owner - 1 ,Owner -2,Owner -3'

'Owner - *' is incorrect please try this - 'Owner *' . * is a wildcard character it picks everything starts with 'Owner'.

Re: Hiding certain rows in a straight table

Qlik_ULG — Thu, 01 Mar 2018 09:33:00 GMT

Hi Ishtdeep.

Neither of these work again. Including 'Owner *' will not work as it is still inside the '' so it will assume that this is the exact text needed.

Are you saying to include these as an expression or calculate dimension?

I need to include multiple ONLY statements. They work singularly and changing the = parenthesis is not doing anything.

Re: Hiding certain rows in a straight table

isingh30 — Thu, 01 Mar 2018 16:04:09 GMT

This is not dimesion it's expression. Try this in expression.