Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
hamza99a
Creator II
Creator II

creating a new field

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

23 Replies
undergrinder
Specialist II
Specialist II

PLease see the attached screenshot

goal-fact.png

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
Creator II
Creator II
Author

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
Specialist II
Specialist II

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

undergrinder
Specialist II
Specialist II

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
Creator II
Creator II
Author

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
Specialist II
Specialist II

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
Creator II
Creator II
Author

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