topic Re: Table box help in QlikView

Table box help

Thu, 04 Sep 2014 12:59:02 GMT

Hi exports,

I have a table box(all are direct values ,we do not use any caculations) with below values.My issue is I am getting two rows for each Order Line item but I need only one row which will have standard cost value.

Here is the IN put table:

Effective Date	Business Segment	Pole	Order Number	Order Type(SO/PO)	Order Line Item	Delivery Priority	Standard Cost - USD
9/4/2014	Critical Power	EMEA	0150012011	SO	000010	02
9/4/2014	Critical Power	EMEA	0150012011	SO	000010	02	11.64000
9/4/2014	Critical Power	EMEA	0150012011	SO	000020	02
9/4/2014	Critical Power	EMEA	0150012011	SO	000020	02	11.64000
9/4/2014	Critical Power	EMEA	0150012011	SO	000030	02
9/4/2014	Critical Power	EMEA	0150012011	SO	000030	02	13278.57000

I need OUTPUT like

Effective Date	Business Segment	Pole	Order Number	Order Type(SO/PO)	Order Line Item	Delivery Priority	Standard Cost - USD
9/4/2014	Critical Power	EMEA	0150012011	SO	000010	02	11.64000
9/4/2014	Critical Power	EMEA	0150012011	SO	000020	02	11.64000
9/4/2014	Critical Power	EMEA	0150012011	SO	000030	02	13278.57000

any suggestions ,

Thanks

Re: Table box help

Gysbert_Wassenaar — Thu, 04 Sep 2014 13:04:01 GMT

Solution 1: load only the records where Standard Cost - USD has a value:

LOAD * FROM ...somewhere... WHERE len(trim([Standard Cost - USD])) > 0;

Solution 2: Add a listbox for the [Standard Cost - USD] field and select all values in it.

Solution 3: Use a straight table instead of a table box and add an expression only({<[Standard Cost - USD]={'*'}>}1), then hide the expression on the Presentation tab.

Re: Table box help

ashfaq_haseeb — Thu, 04 Sep 2014 13:07:20 GMT

May be you need to format date as below while loading it from database.

Date(Floor(Date_Time)) as Date,

Regards

ASHFAQ

Re: Re: Table box help

Thu, 04 Sep 2014 13:50:59 GMT

Thanku Gysbert for your quick reply.
I have tried your 3rd solution but not yet resolved the issue.

I am sharing my sample app pls look into it .

Thanks

Re: Table box help

ashfaq_haseeb — Thu, 04 Sep 2014 13:55:01 GMT

Hi,

use this expression.

only({<[Standard Cost - USD]={'*'}-{''}>}1)

have a look at attached application

Regards

ASHFAQ

Re: Re: Table box help

Thu, 04 Sep 2014 14:05:09 GMT

Thanks Ashfaq, your solution looks good for staright table but do we have any more possible solution in table box,because my client wants me to do it in table box.

Thanks

Re: Table box help

Anonymous — Thu, 04 Sep 2014 14:21:14 GMT

On the Table Box, Properties, Presentation :

Select Standard Cost - US
Tick <Omit Rows Where Field is NULL>

If the blank Standard Cost - US values are not null, then force then to Null in the load script :

if ( [Standard Cost - USD] > 0 , [Standard Cost - USD] ) as [Standard Cost - USD]