topic Re: deriving missing values in QlikView

deriving missing values

Anonymous — Tue, 17 Mar 2015 13:37:28 GMT

Hi All

In attached QV technically it has three sheets(S1, S2, S3) and three scenario(A,B,C). I want to see all available Sheets and Scenarios by keeping these tables seperate. With 'keep' I do not see any difference.

How do I control at DM level.

Thanks

Re: deriving missing values

giakoum — Tue, 17 Mar 2015 13:41:33 GMT

Not very clear what you are trying to achieve. Could you please elaborate a little?

Re: deriving missing values

Anonymous — Tue, 17 Mar 2015 13:48:30 GMT

Ioannis

In my example I have a key which exist in Fact table but not reference(Dimension) table, so data are missing because key does not match between. I need full outer join so I can see everything from both tables.

As we do not know what are missing from fact or dim, that's why full outer join needed; so data can't be missed.

Thanking you

Re: deriving missing values

giakoum — Tue, 17 Mar 2015 13:49:41 GMT

and why don't you use a full outer join to create a new table containing data? QV default.

Re: deriving missing values

avinashelite — Tue, 17 Mar 2015 13:55:00 GMT

Hi Satish,

Qlikview By default will do have full outer join only , do you will not loss any data. Actually your getting all the data in the example file.

If your looking in specific can you please elaborate.

Re: deriving missing values

Ralf-Narfeldt — Tue, 17 Mar 2015 13:55:59 GMT

I also have a hard time seeing what you want to do with the Tab2: load, but if you want to get separate fields for Sheet and Scenario you can just add a preceding load that breaks up ShKey with SubField():

T1:

LOAD *,

SubField(ShKey, '-', 1) As Sheet,

SubField(ShKey, '-', 2) As Scenario;

LOAD * INLINE [

Line, Amt, ShKey

1,2, S1-A

2,5, S2-B

3,8, S3-C

4,3, S1-A

5,4, S2-B

6,6, S3-C

7,7, S1-A

8,9, S2-B

9,1, S3-C

];

Re: deriving missing values

Anonymous — Tue, 17 Mar 2015 14:20:07 GMT

Avinash

By default we will have inner join; so data losing chances always there. In my example you can see S1 sheet and A scenario are missing. The reqs is I want all from both the sides tables.

Re: deriving missing values

Anonymous — Tue, 17 Mar 2015 14:23:39 GMT

Hi Ralf

I appreciate your reply, and helpful too. By your example we can get all required data from one side but not from both. Let say S1 exist in Left table but not in right table. If S4 exist in Right but not in left table. So I need S1, S2, S3, S4 to be displayed in List box.

Thanking you