topic Re: Dynamic Variables in QlikView

Dynamic Variables

Mon, 24 Oct 2016 14:08:24 GMT

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!!

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 15:33:14 GMT

I think you need a crosstable load here:

The Crosstable Load

Re: Dynamic Variables

Mon, 24 Oct 2016 15:47:54 GMT

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

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 15:57:04 GMT

You sure can.

Re: Dynamic Variables

Mon, 24 Oct 2016 16:20:38 GMT

I apparently have made it angry..

What is it upset about?

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 16:48:34 GMT

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;

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 16:49:52 GMT

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.

Re: Dynamic Variables

Mon, 24 Oct 2016 17:49:14 GMT

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?

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 18:17:21 GMT

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

Re: Dynamic Variables

Mon, 24 Oct 2016 18:27:31 GMT

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.

Re: Dynamic Variables

sunny_talwar — Mon, 24 Oct 2016 18:37:21 GMT

Look here for ways to upload a sample:

Uploading a Sample

Re: Dynamic Variables

Mon, 24 Oct 2016 18:55:19 GMT

Ok Sunny--Try this out! I broke the sample rates sheet into 2 tabs. The first tab are the CCP Descriptions that will join with the Labor table to get the actual labor rate. Thanks to you, that part is working like a charm. NOW.. the second tab are standalone rates. I need to store each one into a variable so that I can use it in the application.

Marketing &Sales, COM, COE etc.. but I only need the year that is lining up with the hours I've spent.

Re: Dynamic Variables

sunny_talwar — Tue, 25 Oct 2016 13:05:17 GMT

Robyn -

I have not forgotten you. I am still looking into the issue, just not getting enough time to look at this.

Best,

Sunny

Re: Dynamic Variables

Tue, 25 Oct 2016 18:03:14 GMT

Thank goodness! I appreciate your help. I'm lost on this end. I've tried six ways from Sunday to get this to work am not having success..

Re: Dynamic Variables

sunny_talwar — Tue, 25 Oct 2016 23:33:05 GMT

Try this out:

Left Join(Labor)

LOAD [CCP Description] as Year_F,

[Touch Discrete],

[Mfg Labor (Mfg Proj Mgr)],

[Mfg Labor (Mfg Engineer)],

[Mfg Labor (Mfg Assistant)],

[Eng Labor (Program Mgr)],

[Eng Labor (Senior Systems Eng)],

[Eng Labor (Systems Eng)],

[Eng Labor (Senior Eng)],

[Eng Labor (Engineer)],

[Eng Labor (Senior Sftwr Eng)],

[Eng Labor (Software Eng)],

[Eng Labor (Proj Mgr/Adm)],

[Eng Labor (Associate Eng)],

[Engineering Assistant (E)]

FROM

RatesSample.xlsx

(ooxml, embedded labels, header is 1 lines, table is [Labor Rates], filters(

Transpose()

));

I have not memorized this particular function, but have again used the wizard to transpose the data. Once transposed, I simply renamed CCP Description as Year_F

Here is tutorial on how to rotate your table using the wizard

QlikView Rotating Tables‌

I hope this will help.

Best,

Sunny

Re: Dynamic Variables

sunny_talwar — Wed, 26 Oct 2016 02:05:48 GMT

My bad, it seems that I gave you the script for the wrong tab.

LOAD F1 as Year_F,

[Mfg Labor Overhead],

[Engr Labor Overhead],

[Material Overhead],

[General & Admin.],

[Marketing & Sales],

COM,

COE,

COMatl,

COMS,

COGA

FROM

RatesSample.xlsx

(ooxml, embedded labels, table is [Other Rates], filters(

Transpose()

));

Re: Dynamic Variables

Wed, 26 Oct 2016 14:20:00 GMT

Oh my!! It worked!! You have saved me so much headache! Thank you!!

Re: Dynamic Variables

sunny_talwar — Wed, 26 Oct 2016 16:12:21 GMT

I am glad it is all resolved now. I would request you to close this thread by marking a correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Dynamic Variables

Wed, 26 Oct 2016 20:03:07 GMT

Since you are helping me so much already- can I ask you some more questions? Now that I've fixed the above issue, i'd like to make some variables to simplify my code. As an expression, this works:

=Sum(
If(Account='Subcontracts/CWTs', [Cost Amount],
If([CCP Description]='Mfg Labor (Mfg Proj Mgr)',[Cost Amount]*[Mfg Labor (Mfg Proj Mgr)],
If([CCP Description]='Mfg Labor (Mfg Engineer)',[Cost Amount]*[Mfg Labor (Mfg Engineer)],
If([CCP Description]='Mfg Labor (Mfg Assistant)',[Cost Amount]*[Mfg Labor (Mfg Assistant)],
If([CCP Description]='Eng Labor (Program Mgr)',[Cost Amount]*[Eng Labor (Program Mgr)],
If([CCP Description]='Eng Labor (Senior Systems Eng)',[Cost Amount]*[Eng Labor (Senior Systems Eng)],
If([CCP Description]='Eng Labor (Systems Eng)',[Cost Amount]*[Eng Labor (Systems Eng)],
If([CCP Description]='Eng Labor (Senior Eng)',[Cost Amount]*[Eng Labor (Senior Eng)],
If([CCP Description]='Eng Labor (Engineer)',[Cost Amount]*[Eng Labor (Engineer)],
If([CCP Description]='Eng Labor (Senior Sftwr Eng)',[Cost Amount]*[Eng Labor (Senior Sftwr Eng)],
If([CCP Description]='Eng Labor (Software Eng)',[Cost Amount]*[Eng Labor (Software Eng)],
If([CCP Description]='Eng Labor (Proj Mgr/Adm)',[Cost Amount]*[Eng Labor (Proj Mgr/Adm)],
If([CCP Description]='Eng Labor (Associate Eng)',[Cost Amount]*[Eng Labor (Associate Eng)],
If([CCP Description]='Eng Labor (Eng Assistant)',[Cost Amount]*[Eng Labor (Eng Assistant)],
If(Account='Other ODC',[Cost Amount],
If(Account='Travel ODC',[Cost Amount],
If(Account='Touch/Material',
If((CCP=857 or CCP=858) AND ([Hrs/Qty]<>0), [Cost Amount],
If(Not(Account='Labor')AND (CCP<>857 AND CCP<>858),[Cost Amount],Null()
)//EndIf-Calculates Costs for Touch/Mat'l where CCP are not 857 or 858
)//EndIf-Remove double material overhead
)//EndIf- Touch/Material
)//End Travel ODC
)//EndIf-Calculates Other ODC Costs
)//End If-Eng Labor (Eng Assistant)
)//End If-Eng Labor (Associate Eng)
)//End If-Eng Labor (Proj Mgr/Adm)
)//End If-Eng Labor (Software Eng)
)//End If-Eng Labor (Senior Sftwr Eng)
)//End If-Eng Labor (Engineer)
)//End If-Eng Labor (Senior Eng)
)//End If- Eng Labor (Systems Eng)
)//End If- Eng Labor (Senior Systems Eng)
)//End If- Eng Labor (Program Mgr)
)//End If- Mfg Labor (Mfg Assistant)
)//End If- Mfg Labor (Mfg Engineer)
)//End If- Mfg Labor (Mfg Proj Mgr)
)//EndIf- Subcontract/CWT
)//EndSum

But as a variable it just repeats the total value for each CCP Description. I've tried the Aggr function and that drives the result to Null.

Re: Dynamic Variables

sunny_talwar — Wed, 26 Oct 2016 20:19:39 GMT

When you are doing Aggregation in the script, you usually need to use Group By statement. It is difficult to explain how this can be done since your expression is not very simple.