Table box help

[#1]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[ashfaq_haseeb]

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

Date(Floor(Date_Time)) as Date,

Regards

ASHFAQ

[#4]

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

[ashfaq_haseeb]

Hi,

use this expression.

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

have a look at attached application

Regards

ASHFAQ

[#6]

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

[Anonymous]

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]