Formula for blank value in lookup function

pnataraj

Hi

I am using the below formula for one of my line item in my module 'Demand'. I am doing a look up in another module 'Open Qty by Bucket ID'  by Bucket ID and SKU to populate the open qty value in the 'Demand' module.

 

Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF]

 

The issue I see here is,if there is no matching value returned from the look up, I am seeing open qty being populated as 0. When there is no matching,I need to show the Reqd qty ( another line item in the same 'Demand' module).

I tried with the below formula:

 

IF Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF] is BLANK  THEN Quantity Reqd ELSE Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF]

 

But I am getting invalid formula error.

Any help would be appreciated.

pnataraj

Thanks a lot,,It worked!

Kyle

Hello, 

 

If the open quantity is number formatted, then you would have to write IF Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF] = 0  THEN Quantity Reqd ELSE Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF] because the 'BLANK' statement is for text, time, date or list formatted line items. 

 

If the issue that is causing the blanks is because the 'Bucket ID LF' or 'SKU LF' line items aren't always populated, then you could do IF ISBLANK(Bucket ID LF) OR ISBLANK(SKU LF) THEN Quantity Reqd ELSE Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF]. 

 

I hope this helps!

 

Kyle

Glen_Prager

"is BLANK" is not valid syntax.  Try this:

 

IF Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF] = 0 THEN Quantity Reqd ELSE Open Qty by Bucket ID.Open Qty[LOOKUP: Bucket ID LF, LOOKUP: SKU LF]

pnataraj

Hi

I have two columns in my data file. One is Sysdate ,in MM:DD:YY:HH:MM format and another one  (Purchase Date) in MM:DD:YY format. I need to subtract Sysdate- Purchase Date to get the Age of the asset. My approach is

1) Define the Sysdate column as Text format  and Purchase date column as Date format (MM:DD:YY)

2) Convert the Sysdate into date format  (MM:DD:YY)  in another column ( Sysdate-Modified)

3) Then subtract Sysdate-Modified from Purchase Date.

Please let me know this is a correct approach and also the formula to convert the text format into time format  (MM:DD:YY).

 

Thanks

Regards

Prabakaran

Kyle

Hi Prabakaran,

 

Your approach sounds appropriate to me. You can use a combination of the LEFT and RIGHT functions to convert the text into the format necessary to then use the VALUE function to convert the text into numbers that can then be used as input into the DATE function. I recommend using multiple line items for testing purposes and then you can consolidate them into a single line item later. I hope this helps!

 

Kyle

Egor

Hi,

 

The approach is correct. You want to use DATE function when converting text to date format. The function takes in value for year, month, and day (ex. DATE(2016,12,31) ) that you can extract from your text field using LEFT and RIGHT functions (they work exactly the same way as in Excel).

 

Note that DATE function only takes number values, while LEFT and RIGHT functions return text. It is very easy to control for this with VALUE function.

 

If your date is formatted as MM : DD : YY : HH : MM, the formula you are looking for is:

DATE(2000 + VALUE(RIGHT(LEFT('Date (text)', 8), 2)), VALUE(LEFT('Date (text)', 2)), VALUE(RIGHT(LEFT('Date (text)', 5), 2)))

See screenshot attached for more detail.

pnataraj

Thank you very much, Egor! It worked.

I am trying to use the below formula for the line item ' Age in Days-Copy'.

'Sysdate - Report Date (Date)' But getting invalid message.

Can you please help to find out where did I go wrong?

I have attached the screen shot.

 

Thanks

Regards

Prabakaran

 

Kyle

Hi Prabakaran,

 

You have to put apostrophes around the Report Date (Date) line item because of the parenthesis. Attached is a screenshot to show the formula. I hope this helps!

 

Kyle