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]

Look here for ways to upload a sample:

Uploading a Sample

[#3]

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.

[sunny_talwar]

Robyn -

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

Best,

Sunny

[#5]

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

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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()

));

[#8]

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

[sunny_talwar]

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

[#10]

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.

[sunny_talwar]

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.