Completing missing data

ranibosch — Fri, 03 Nov 2017 06:07:49 GMT

Hi,

my model currently works as follows:

1. I have created a table where I pull all my different document type headers with the field, document type and responsibiliy center.

This means that my table looks as follows:

Document No Responsibility Center

PPINV99 WR

PCN39 MT

2. I have got a general ledger entries table where I have a list of all the transactions

Table looks as follows:

Year Document No Dep Amount

2017 PPINV99 AMS 566

2017 PCN39 MONT -50

2017 MTWH0101 NPD 982

3. I have then joined table 1 + 2 to form 1 table to look as follows:

Year Document No Dep Amount Responsibility Center

2017 PPINV99 AMS 566 WR

2017 PCN39 MONT -50 MT

2017 MTWH0101 NPD 982

4. My question is as follows:

There are entries in the general ledger (like example a journal) that is not allocated to a responsibility center. The reason being is that it does not contain a responsibility center field on any document header (first table a pulled). (my result in qlikview is either a blank or a "-" in the responsibility center field)

I would thus then for the remainder of the transactions (not allocated) look at the department field and use that to allocate the remainder of the transactions.

In above scenario, if the Dep = NPD, then my Responsibiliy Center = WR

Re: Completing missing data

arvind_patil — Fri, 03 Nov 2017 10:20:23 GMT

HI Rani,

May be code like below:

You Get Below Table

3. I have then joined table 1 + 2 to form 1 table to look as follows:

Year Document No Dep Amount Responsibility Center

2017 PPINV99 AMS 566 WR

2017 PCN39 MONT -50 MT

2017 MTWH0101 NPD 982

We Consider it As it as table C:

Noconcatenate

😧

Load Year ,

[Document No],

Dep ,

Amount ,

[Responsibility Center],

If(isnull([Responsibility Center]),'WR',[Responsibility Center]) as [New Responsibility Center]

resident C;

Drop Table C;

Thanks,

Arvind Patil

topic Completing missing data in QlikView

Completing missing data

Re: Completing missing data