topic Re: Problem with value in linked table in QlikView

Problem with value in linked table

embruqlikview — Wed, 25 Nov 2020 16:16:04 GMT

Hi there

I connect two tables. One with the facts, the other one with budget.

for both tables, i created a key with autonumber.

Now, in both tables, the key are matching. But there are rows, where the some values are missing.

I added a picture:

the red area represents the budget table, the blue one the facts table. As we see, the key number 509 or 510 are correct in the blue area. But the key 508 has no values vc and wb. The Values VC=16 and WB=62 exists in the Fact-Table, but not shown by key 508.

I tried several keys, with and without Autonumber.

What i'm doing wrong?

Thanks for your help

Rolf

Re: Problem with value in linked table

vinieme12 — Sat, 24 Dec 2016 07:47:10 GMT

So do you want the Fact table to have the same values as you have in the budget table?

then use Mapping Load

B_VC_MAPPING:

Mapping Load

KEY,

B_VC

FROM Budget;

B_WB_MAPPING:

Mapping Load

KEY,

B_WB

FROM Budget;

During FACT load add an extra validation to check and replace for nulls in VC and WB

FACT:

LOAD * ,

if(isnull(VC) or len(VC)<1,Applymap('B_VC_MAPPING',KEY),VC) as VC,

if(isnull(WB) or len(WB)<1,Applymap('B_WB_MAPPING',KEY),VC) as WB,

FROM YOURDATASOURCE;

But then what is point in having the same info in two separate tables??

Re: Problem with value in linked table

embruqlikview — Sun, 25 Dec 2016 01:09:47 GMT

Thank you for your fast answer.

i can’t map the values from the budget table to the fact table.

Because the Fact-Table contains the main data. The sum of them must be comparised to the budget table.

In the Budget Table i do not need the field b_vc and b_wb - they are only to control the selection and the creation of the right key.

I added another picture of both tables. The Budget table is correct. But the key 508 points to the Fact-Record with the key 508 and here i loose the values WB and VC.

The key e.g. 509 is correct.

Is this maybe a problem of the keys?

I build the keys on this way:

LOAD
AutoNumber (VC&'-'&WB&'-'&Jahr) as Key_Budget,
VC as B_VC,
    WB as B_WB,
    Budget,
    Jahr as B_Jahr
FROM

(qvd);

And in the Fact-Table:

AutoNumber (Vertreterschlüssel_1&'-'&Warenbereich&'-'&Mid(Auftragsdatum,1,4)) as Key_Budget,

Re: Problem with value in linked table

vinieme12 — Sun, 25 Dec 2016 07:40:20 GMT

OK,

I think your table is a Straight Table

With Dimension = Key_Budget

Expression1 = WB

Expression2 = VC

Expression3 = Budget

Is this correct?

You have different values for VC and WB that is why they are showing as '-'

try SUM(WB) and SUM(VC)

move WB and VC to dimensions there must be multiple different values for this.

Re: Problem with value in linked table

embruqlikview — Mon, 26 Dec 2016 01:51:59 GMT

I added a sample qvw, it's simplier to show my question.

If you select in the list boxes above B_Jahr=2016 and B_VC=16 then you can see 4 budget-records with these amounts: 2000, 2000, 6000, 20000. The sum is 30'000.

Then clear the selections and select in the list boxes below Jahr=2016, Vertreterschlüssel_1=16, then we can see, that in the budget table only 3 records with the amount of 28'000 are available

Re: Problem with value in linked table

vinieme12 — Mon, 26 Dec 2016 11:49:54 GMT

because in FACT Warenbereich 62 Doesn't exist for VC 16 , it Exist for VC = 22

WB = 62 exists for WC = 22

Re: Problem with value in linked table

embruqlikview — Fri, 30 Dec 2016 15:05:31 GMT

I see,

Thats exactly my problem.

Is there a way to generate the missing records in Facts Einzelposten during the load of Budget. Maybe that would resolve my problem. I found some examples in the community, but these show only small tables. I have to fill the key fields (B_YEAR->YEAR, B_VC -> VC, B_WB -> WB) and all other fields with initial values chosen by me.

Thanks for your help

Rolf

Re: Problem with value in linked table

Miguel_Angel_Baeyens — Fri, 30 Dec 2016 15:21:24 GMT

You can use an outer join while loading the two tables, store into an QVD, then load that QVD and deal with the possible null values for clarity.

See more on the topic here: Generating Missing Data In QlikView | Qlik Community