Help Grouping together Rates with similar texts

[krishnagandhi]

Hi,

I have attached my data below and workflow. i have my data to a point where i need to group together rates for equipment, each piece of equipment has a day rate, week rate and month rate. However because the description is not exactly the same i can not summarize it to collapse the date. to further complicate matters sometimes the description has DY or Day; Month or Mon; Week or Wk. Is there an easy way to get the Day/Month/Week rates so they show up in one row, preferably showing the product# of the Day rate. This will enable me to link it to my testing data to run rate checks.

Thanks,

Krishna  

[T_Willins · Accepted solution]

Hi @krishnagandhi,

See the attached workflow.  This should meet your requirements.  It looks at the first letter of the Unit of Measure to determine if it is a Day, Week, or Month (lower case and upper case both considered).  It then trims the Unit of Measure from the short and long texts before Cross Tab, Data Cleanse, Summarize and Select (to arrange fields).

[krishnagandhi]

So it looks like it almost worked, but for a lot of them (Compressor, Air, 125-199 CFM VOL is a good example) where the week and month rates collapsed into one line, but the Day rate is on a separate line.

[T_Willins · Accepted solution]

Hi @krishnagandhi,

The long text is different, so it will not group with the line above it.  You could drop the group by Long text and only take the first or last Long text instead.

[krishnagandhi]

Hi @T_Willins , it works when i drop the long text from the summary fields. Thanks!