Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Re: creating a new field

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.

View solution in original post

Highlighted
Creator II
Creator II

Re: creating a new field

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

Highlighted
Specialist II
Specialist II

Re: creating a new field

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.

Highlighted
Creator II
Creator II

Re: creating a new field

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

Highlighted
Creator II
Creator II

Re: creating a new field

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

Highlighted
Specialist II
Specialist II

Re: creating a new field

Highlighted
Specialist II
Specialist II

Re: creating a new field

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.

Highlighted
Creator II
Creator II

Re: creating a new field

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

Highlighted
Specialist II
Specialist II

Re: creating a new field

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.

Highlighted
Creator II
Creator II

Re: creating a new field

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