Formula for blank value in lookup function

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.

Accepted answers

pnataraj

Thanks a lot,,It worked!

All comments

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

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.

Screen_Shot_2017-04-28_at_18_7611.png

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

Screen_Shot_2017-04-28_at_11_7625.png

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

Sysdate_-_Report_Date_7662.JPG

pnataraj

Thanks a lot,,It worked!

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…