topic Re: Problem linking Fieldnames from different tables in QlikView

Problem linking Fieldnames from different tables

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

See here a problem that I’m struggling to find a solution.

The main problem can be attributed to message “One or more loops have been detected in your database structure”.

I want to create a plot actual costs versus budget costs.

Blue line is actual, representing cumulative costs.Red line is the budget. Budget values changes from month to month.

The Table box contains the data:

You can see that the date, created from MasterCalendar, frequently shows no costs.The plot works fine, but:

when I click on costs group EAG from this list:

1) Then the plot does not show all days of the months (from 1 jan 2016 up to today.

2) In addition, the budget (red) line is not correct because it did not select the correct costs group. Not a surprise since the fieldname “Group_” for defining Costs group in the “Budget3” table cannot be linked with ‘Group” from “Expenditure” table. Converting “Group_” into Group gives me the error reporting : One or more loops have been detected in your database structure”.

Do you see any solution how to solve this?

Best regards,

Cornelis

Re: Problem linking Fieldnames from different tables

sunny_talwar — Fri, 04 Mar 2016 10:42:51 GMT

Is this the correct visual after selection?

If the above is correct, I just unchecked 'Suppress Zero-Values' and 'Suppress Missing' on the presentation tab

Re: Problem linking Fieldnames from different tables

Gysbert_Wassenaar — Fri, 04 Mar 2016 10:55:21 GMT

Try this instead:

Facts:

LOAD Date(date#(date,'DD-MM-YYYY'),'DD-MM-YYYY') as date, Costs, Group INLINE [

date, Costs, Group

01-01-2016, 2586, TSG

03-01-2016, 9981, TSG

10-01-2016, 1258, TSG

13-02-2016, 13000, TSG

05-01-2016, 1001, EAG

18-01-2016, 900, EAG

23-02-2016, 578, EAG

01-03-2016, 1257, EAG

]

;

Concatenate(Facts)

LOAD Date(date + IterNo() -1) as date, Budget/(Ceil(MonthEnd(date)-MonthStart(date))) as Budget, Group

WHILE date + IterNo()-1 <= MonthEnd(date);

LOAD Date(Date#(month & '-2016','MMM-YYYY'),'MM-DD-YYYY') as date, Budget, Group INLINE [

month, Budget, Group

jan, 25000, TSG

feb, 10000, TSG

mar, 1300, TSG

jan, 5000, EAG

feb, 1500, EAG

mar, 1300, EAG

]

;

Then create your calendar using the date field. You can use that same date field as chart dimension as well.

Re: Problem linking Fieldnames from different tables

Fri, 04 Mar 2016 10:56:03 GMT

Dear Sunny,

You have just solved the first problem, namely the plot shows all data points.

The second problem is the red budget line. this is not the correct representation, I (we) need to see a linear increase over days and that changes from month to month.

looks like a challenge.

Thank you for answering the first question, this is useful.

Best regards,

Cornelis

Re: Problem linking Fieldnames from different tables

sunny_talwar — Fri, 04 Mar 2016 13:22:43 GMT

Does this resolve your second issue?

Expression for budget: SUM({<Group_ = p(Group)>}[Budget YTD])

Re: Problem linking Fieldnames from different tables

Colin-Albert — Fri, 04 Mar 2016 14:19:55 GMT

Try this version. It concatenates the budget data to the transactions and sets the budget date as the first of the month.

The areas changed are shown below.

TempData:
LOAD *
INLINE [
date, Costs, Group
01-01-2016, 2586, TSG
03-01-2016, 9981, TSG
10-01-2016, 1258, TSG
13-02-2016, 13000, TSG
05-01-2016, 1001, EAG
18-01-2016, 900, EAG
23-02-2016, 578, EAG
01-03-2016, 1257, EAG
]
;

Expenditure:
LOAD
Costs,
Group,
// date(date, 'D-M-YYYY') as PostgDate
date(date#(date, 'DD-MM-YYYY')) as PostgDate

Resident TempData;

Drop table TempData;

concatenate (Expenditure)

load

makedate('2016', match(month, 'jan', 'feb', 'mar')) as PostgDate, // creates the Posting Date for the Budget as 1st of the month

Group_ as Group,

Budget

INLINE [

month, Budget, Group_

jan, 25000, TSG

feb, 10000, TSG

mar, 1300, TSG

jan, 5000, EAG

feb, 1500, EAG

mar, 1300, EAG

]

;

Re: Problem linking Fieldnames from different tables

Colin-Albert — Fri, 04 Mar 2016 14:21:32 GMT

Here is the result.

Re: Problem linking Fieldnames from different tables

Sat, 05 Mar 2016 05:46:52 GMT

Dear Colin,

Thank you for your swift reply. I have checked it and the plot with red budget line is not correct, unfortunately.

See here the correct line:

At Jan 1st, the budget = 0.

At the end of january the budget score must be 30000. There is a linear increase between 0 and 30000.

Budget for February = 11500.

February first budget = 30000 and at the end of February 30000 + 11500 = 41500.

There must be also a linear increase between 30000 and 41500.

The ditted black line is the coreect budget representation.

So my question, do you see any solution to make this event happen in QV?

Thanks and best regards,

Cornelis.

Re: Problem linking Fieldnames from different tables

Sat, 05 Mar 2016 06:01:42 GMT

Dear Sunny,

Thank you for your reply. The option Group_=p(Group) looks interestring, taht is for me a new learning point.

If I select no group, then we will see this:

However, when I select e.g. EAG, the the plot with budget line is not correct:

The budget for EAG is for end January 5000, so a linear increase from jan 1st = 0 to 5000 Jan 31st.

The is not viewable in the plot.When you select no group, then the plot shows the correct linear increase.

Hope that you will see any solution.

Best regards,

Cornelis

Re: Problem linking Fieldnames from different tables

sunny_talwar — Sun, 06 Mar 2016 00:07:47 GMT

Is this what you would like to see?

Expression: SUM({<Group_ = p(Group), Group>}[Budget YTD])

Re: Problem linking Fieldnames from different tables

Mon, 07 Mar 2016 06:46:06 GMT

Dear Sunny,

Yes, that is eactly what I'm looking for.

The expression SUM({<Group_ = p(Group), Group>}[Budget YTD])

is showing me a linear increase (cumulative sum) over month days.

Nice and very instructive expression!

This excellent and a big improvement.

Thank you for all your effort