creating a new field

[hamza99a]

Hello all,

I have created a table that shows Account Type, the region, and the balance amount for the day. I now want to show percentage of my yearly goal for each region and each account type. I have created an excel file that has the same dimension names, yet when I add them in and try to do a calculation with the goal, it seems that it appears null. If anyone knows how to help Id greatly appreciate it.

Thanks,

Hamza

[undergrinder · Accepted solution]

PLease see the attached screenshot

You need proper create the data model.

My load script was based on your sample:

Goals:
LOAD
    upper("Goals Branch Region" &'-'& GoalsGovAccType) as Key,
    "GOALS:" as Goals
FROM [lib://download/Sample data Goals (1).xlsx]
(ooxml, embedded labels, table is Sheet1);

Facts_tmp:
LOAD
    "ACCT NBR",
    GovAccType,
    "Current Balance",
    "Production Date",
    "Tax ID",
    "Balance Previous Year",
    "Open DT",
    "Close DT",
    "Branch Number"
FROM [lib://download/Sample Datareal.xlsx]
(ooxml, embedded labels, table is Deposits28);

Left join(Facts_tmp)
LOAD
    "Branch Region",
    "Branch Number"
FROM [lib://download/Sample Datareal.xlsx]
(ooxml, embedded labels, table is Branch);

Facts:
Load
    "ACCT NBR",
    GovAccType,
    "Current Balance",
    "Production Date",
    "Tax ID",
    "Balance Previous Year",
    "Open DT",
    "Close DT",
    "Branch Number",
    "Branch Region",
    upper("Branch Region" & '-' & GovAccType) as Key
Resident Facts_tmp;

drop table Facts_tmp;

G.

[hamza99a]

Hello I am sorry but the original data called sample data does not have a branch region. I have a different file called branch where it includes branch region and branch number. Maybe I need to join them based on that.

Thanks

[undergrinder]

just follow the logic I provide in the example.

It doesn't matter from which table come from the branch number - brach region dictionary.

G.

[hamza99a]

The problem I am getting is that the Gov Acc Type is not linking. So essentially I need deposits curr bal amt, Goals, Gov Acc Type, and Branch Region all to line up. When I do the join it creates 3 synthetic keys. Please find image below of the error I am getting.

Thanks for your help

[hamza99a]

also when i tried the left join portion, it crashed my qlik sense, not too sure why

[undergrinder]

You get a circular reference.

See the link below

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/LoadData/understand-circular-refer...

[undergrinder]

I upload a qvf file, please look into it.

Goal vs actual.qvf

If you have further concern please share your qvf, in order to debugging.

[hamza99a]

The one thing that I dont think you understand is that I do not have branch region in my sampledatareal file. I understand the join and everything, however I do not have branch region in the file with deposits and the balances. If you could get it to work that way, it would be much appreciated.

Thanks

[undergrinder]

I understand that, the Branch region and branch number is on other granularity level, but you can rearrange the data that way to have a branch region.

I think I misunderstand something

I won't spam this thread further, I hope you manage the problem.

G.

[hamza99a]

I am also using a qvd file for this, if that changes anything. Thanks