topic Re: creating a new field in App Development

creating a new field

hamza99a — Tue, 14 Aug 2018 18:45:51 GMT

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

Re: creating a new field

hamza99a — Tue, 14 Aug 2018 18:46:17 GMT

stalwar1‌ mahlerma‌ youssefbelloum‌ johnw‌ csg‌

Re: creating a new field

undergrinder — Wed, 15 Aug 2018 08:41:40 GMT

hi,

could you provide us an example?

Have you already got a year-to-date data or this is also part of the task?

Re: creating a new field

BalaBhaskar_Qlik — Wed, 15 Aug 2018 14:46:12 GMT

Provide the expression or a screen shot...

Try like this:

Sum({< Year = {'2018'},[Account type]=,Region= >} [Balance Amount])/Sum(total {< Year = {'2018'},[Account type]=,Region= >} [Balance Amount])

Re: creating a new field

hamza99a — Wed, 15 Aug 2018 14:48:00 GMT

I currently have a vizlib sparkline table organized by branch region, then govACC Type. I would like to input goals into the table so that I can use it for comparison purposes. I created some sample data of the deposits table and the goals table that I had just created. For some reason the goals are not making the connection into the table. I also cannot use if statements in the table as it makes the sparklines disappear. I also tried to left join the tables based on branch numbers, but that caused qlik sense to crash. Any help would be greatly appreciated.

Re: creating a new field

hamza99a — Wed, 15 Aug 2018 14:55:32 GMT

davidstorek‌ stalwar1‌ youssefbelloum‌ undergrinder‌

Re: creating a new field

hamza99a — Wed, 15 Aug 2018 15:17:11 GMT

I even tried adding in the goals table manually and I get this error. stalwar1‌ please help

Re: creating a new field

DavidŠtorek — Thu, 16 Aug 2018 06:08:22 GMT

Hello,

try this post % of Goal it is the simmilar to what you are asking

Re: creating a new field

DavidŠtorek — Thu, 16 Aug 2018 06:09:54 GMT

Actualy it is the same

Re: creating a new field

undergrinder — Thu, 16 Aug 2018 09:12:54 GMT

Hi Hamza,

I checked the attached files,

The goals and the facts can connect though GovAccType - GoalsGovAccType column - did you rename these columns to share the name?

And the Branch number should be changed to Goals Branch Region or vice versa, to fulfill the key's requirements.

Re: creating a new field

undergrinder — Thu, 16 Aug 2018 10:37:21 GMT

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;

Re: creating a new field

hamza99a — Thu, 16 Aug 2018 13:59:02 GMT

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

Re: creating a new field

undergrinder — Thu, 16 Aug 2018 14:02:12 GMT

just follow the logic I provide in the example.

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

Re: creating a new field

hamza99a — Thu, 16 Aug 2018 15:19:10 GMT

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

Re: creating a new field

hamza99a — Thu, 16 Aug 2018 15:47:53 GMT

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

Re: creating a new field

undergrinder — Fri, 17 Aug 2018 07:19:39 GMT

You get a circular reference.

See the link below

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

Re: creating a new field

undergrinder — Fri, 17 Aug 2018 07:23:32 GMT

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.

Re: creating a new field

hamza99a — Fri, 17 Aug 2018 12:04:10 GMT

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

Re: creating a new field

undergrinder — Fri, 17 Aug 2018 12:18:01 GMT

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.

Re: creating a new field

hamza99a — Fri, 17 Aug 2018 12:24:00 GMT

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