Guys,
I would like to ask for your assistance on how I am going to add the date as part of the header. See sample below:
Raw Data
Results
Hoping for your help on this.
Thanks,
Kamen
This should work based on your sample data set.
You can use the Dynamic Rename tool for this. Set it as formula, select the column to rename and then use the field name + whatever you want the date to be.
Thanks @cjaneczko . However can you please provide me a sample workflow that will allow the report to be updated everytime the date changes. This is a daily report and date need to be updated. Others are also welcome to provide me solution. I have been thinking this overnight and I have not been able to produce a solution. Sample results with date updated are as follows.
Looking forward for your response on this inquiries.
Hi @KamenRider
Something like the following is what @cjaneczko explained. This is set to find final price as today (as of run time) and old price as yesterday. You could also wrap it in a DateTimeFormat if you need it to be in a specific string format.
Is it always Todays Date, and yesterdays date? Or is there a date field in your report to query on? I believe @JamesCharnley has the results for Current date and yesterdays date. If its more complex we'd need to see more information.
HI @cjaneczko @JamesCharnley
I am trying to replicate your solution however, I am encountering this kind of error. Please advise what went wrong.
Please advise
Its 'Today's Final Price'. You cant mix apostrophes as it thinks what's in quotes is 'Today'. if you have a String with an apostrophe in it, use Double Quotes. Change the first line to this:
IF [Name] = "Today's Final Price"
@cjaneczko @JamesCharnley
I have another problem. the date did not populate in the prior final price. The formula seems to be correct.
Go back to the Join, or if you have a Select tool and look at the size of the fields. Make sure that the V_String fields have enough room to accommodate the Date plus the Prior Final Price text. Its likely trimming Field 7 down to 16ish characters. Youll need to add at least 10 to that and make it 26+ characters in length to accommodate the entire field.
I have added 40 to increase the length to 80 but nothing change.
You need to go back to the tools before the MultiRow formula. The string has already been trimmed, so adding the select tool after it isnt going to fix it. Look at the append tool.
I have increased the length of Name field from 40 tp 256 from append tool but the results is still the same.
Please advise.
Its looking like an issue with your date field. The formatting doesn't look correct so the TODATE function isn't working properly. Without the actual workflow its difficult for me to troubleshoot it.
Take a look at the DateTime help.
ToDate ToDate(x): Converts a string, number, or date-time to a date. An incoming string should be formatted as YYYY-MM-DD. For example, 2020-10-31. An incoming number should be formatted as an Excel date format where the number represents the number of days since 01-01-1900. For example, 7000 which corresponds to 03-01-1919. An incoming date-time should be formatted as YYYY-MM-DD hh:mm:ss. For example, 2020-10-31 12:00:00.
I know it is really important for your to see the workflow but sad thing we are not allowed to send it which is also being block. I am sending you another screenshots which maybe could help.
Try this. Its the formatting of the date field coming into the multirow formula. It needs to be converted back to the proper date format, then if you want it to display as MM/DD/YYYY it has to be formatted back.
elseif [Name] = 'Prior Final Price' then datetimeformat(todate(datetimeadd(datetimeparse([Max_Date],'%m/%d/%Y'),-1,'days')),'%m/%d/%Y') + ' ' + [Name]
HI @cjaneczko
Thanks. Your formula works. Thank you for the patience in helping solve the problem. I learned something.
Next time again, hope you are available to help me.
Glad it worked out and sorry it took so long for me to nail down the issue.
