Uncollapse Columns or Dynamic Pivot or ??

LLucinski
LLucinski Member
in Magic ETL

Greetings -  I'm working on a Finance Dashboard and I've been given a data set in Excel that pulls metrics from various source files via a VLOOKUP; this is updated monthly with current month and year to date values.  I can create most of the cards I need from this data, however, I need to calculate a new metric.  The problem is the way the file is set up -- see snapshot below.  The two metrics that I need to use to produce the new metric are in the same column -- C.  

 

FS Upload.PNG

I need to produce the Outpatient discharges which is Adjusted Adm/Disch - DISCHARGES.  I thought about an ETL using UNCOLLAPSE COLUMNS, but I have 431 monthly metrics.  Any thoughts on how to best proceed?  

Discharges.PNG

Thanks,

 

Laurie L.

Comments

  • GrantSmith
    GrantSmith Coach

    Hi @LLucinski ,

     

    You could utilize a beast mode with a case statement to calculate the outpatient discharges

     

    For example:

     

    CASE WHEN `Filter Field` IN ('DISCHARGE VALUE1', 'DISCHARGE VALUE2') THEN `Metric Field` END

     

     

    That will then only contain the metric you're utilizing if it's a discharge record. You'll also want to make sure you save the calculation on the dataset (click the checkbox in the lower right of the beast mode window) so you can reuse this field on other cards.

     

    Screen Shot 2020-04-06 at 8.11.55 PM.png

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • LLucinski
    LLucinski Member

    Thanks for the suggestion!  I will give this a try and let you know how it turns out.  Laurie

  • GrantSmith
    GrantSmith Coach

    Hi @LLucinski 

    Were you able to get this to work?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • LLucinski
    LLucinski Member

    No, I was not able to get it to work.  I just couldn't get beyond the issue that the both of the metrics I needed to perform the operation were in the same column.  I went back to the requester to ask that the metric be added to the source file.  

     

    I do appreciate your help though!

     

    Thanks.

  • GrantSmith
    GrantSmith Coach

    The CASE statement I mentioned above should handle this scenario which will combine the metric into the same column if the stat types are different. What does your CASE statement look like?

     

    It should look something like:

    CASE WHEN `Line Item / Metric / Stat` IN
    ('DISCHARGES', 'Adjusted Adm/Disch') THEN
       `Line Item / Metric / Stat`
    END

    You can then wrap that in a COUNT aggregate function to get the number of discharge line items.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories