Can qv read number and charater at the same time ?

[#1]

Hi All

My raw data from excel , some of the value = on request.

when i use below script to read :-

KeepChar([Price(Euros)],'1234567890.') as [LIST_PRICE],

it will not capture those value = on request.

when i use below script to get the LAND_COST_TDS_SGD :-

ROUND(LIST_PRICE*(1-Discount)*$(TRANSPORT_SG)*$(EURO_SGD),0.01) as LAND_COST_TDS_SGD

My question is how to make Column 3 null value display as on request ?

Paul

[DavidFoster1]

Crude but effective

=if(LIST_PRICE_RAW='on request','on request',num(LAND_COST_TDS_SGD))

[#3]

Hi David

Thank you very much for your sharing , it work on the table . i just tested.

But my requirement for this case is not put the expression on table , what i need is put the expression on load script. ( As you can see the LIST_PRICE amount is diff from the LAD_COST_TDS_SGD

I try to use LIST_PRICE field see does it work :-

if(LIST_PRICE='on request','on request',num(LIST_PRICE)) as new

But it never capture the on request wording.

Hope you can advise me again.

Paul

[DavidFoster1]

The if statement approach should work nicely in the load script as well for creating a new field. I would suggest that you use a preceding load script so that you can reuse the field LAD_COST_TDS_SGD instead of maintaining that logic twice.

[#5]

Hi David

I have 12 diff price list , only one of the price list have on request , so i need to use reload script. now my issue is i am not able make QV read charater using your expression. as it never read in on request. i aware expression put at table it work fine.

[#6]

Hi David

I also aware that i can use preceding load script , but my issue it does not work. if you look at my zip file , i have the preceding load TAB.

[tresesco]

Hi Paul,

PFA. Click button to view/hide null values.

Hope this is what you want.

[DavidFoster1 · Accepted solution]

In the partial reload TAB I would change the script to be:

left join

(BECKHOFF)

Load

	PART_NO_,
	LISTPRICE,   // it can see the value
	LAND_COST_TDS_SGD,

    if(LIST_PRICE_RAW='on request','on request',num(LAND_COST_TDS_SGD)) AS [FIELD NAME];

Load

*,

    'EURO '&LIST_PRICE as LISTPRICE,   // it can see the value

    ROUND(LIST_PRICE*(1-Discount)*$(TRANSPORT_SG)*$(EURO_SGD),0.01) as LAND_COST_TDS_SGD

Resident BECKHOFF;

[#9]

Hi Tres

Your button very powerful. I can use this for fast filter.

Thank you.

Paul

[#10]

Hi David

Your script even more powerful. notice that it work now using precedding load script. many thank. By theno way i t able to apply this in my actual qv doc , as usual i need to re-look at your script tomorrow morning , when i mind is fresh from bed.

May be you can share with me why you modify the preceding load script from my traditional way to your way ? why second part you need to load * ? h

Thank you for your help.

Paul