Dynamic Variables

[#1]

Hi everyone!! I need some help with a dynamic variable. My source excel file is set up like this:

Right now when I pull the data into Qlik it sees the data like this:

I need the years to be a single 'Year' variable so that I can join it to my other data source (Hours). Currently i'm having to set up if statements to check the year coming from the 'Hours' source and then manually  tell the code to use the rates from [2015]. I want this to be a dynamic effort so that as we add additional rates in the coming years the code will adjust. Help!!

[sunny_talwar]

I think you need a crosstable load here:

The Crosstable Load

[#3]

That sounds perfect! Can I still use a left join to another table once complete?

[sunny_talwar]

You sure can.

[#5]

I apparently have made it angry..

What is it upset about?

[sunny_talwar]

Hahahaha because you will have to do the join after the CrossTable is done. Something like this

Rates:

CrossTable(Year, Rate)

LOAD [CCP Description],

          [2014],

          [2015],

          [2016],

          [2017],

          [2018]

FROM .....;

Left Join(Labor)

LOAD *

Resident Rates;

DROP Table Rates;

[sunny_talwar]

I would suggest using CrossTable Wizard (https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Crosstable_Wizard.htm‌) if you are doing this for the first time so that you don't mess up with the order of the fields and other details.

[#8]

That worked! Follow-on Question: In my table I have a CCP description called Marketing & Sales and a corresponding rate for each year.  In most cases in my app, my system has a CCP Description that will link it to the right rate.

What if I just need to do a formula within the app like below but need to force it to go to the rate for Marketing and Sales?

[sunny_talwar]

I am not sure I understand the question Robyn. Would you be able to provide a sample?

[#10]

Its not letting me attach a file.. so here are some screen shots. Hopefully this helps. Here is my rates file

Here is where i'm pulling it into Qlik and joining it by Year, CCP Description to get Rates:

The items in yellow wont tie to a CCP Description from the Labor system. These are just rates that we use for overhead costs. So I need to be able to access that rate some other way.