topic Re: Remove unecessory data in QlikView

Remove unecessory data

Anonymous — Wed, 15 Jul 2015 09:54:27 GMT

hi,

I need to remove data from dimension and fact table which is not required. I tried both keep and exists functions but somehow couldn't figure out how to eliminate the rows i don't want.

I also attached qvw and see if you can help me in this.

Many thanks,

aamertaimor

Re: Remove unecessory data

qlikmsg4u — Wed, 15 Jul 2015 09:59:10 GMT

You want to remove unwanted data from Data model or in just front end?

If it is front end create a straight table and add Workorder1 and Name as dimensions and Sum(pay) as expression.

don't forget to check Suppress when Value is Null in Name Dimension

Re: Remove unecessory data

Anonymous — Wed, 15 Jul 2015 10:04:44 GMT

Thanks QIKmsg4u but i want this in the script because i have very big table. I think bringing in front end will consume lot of memory.

Re: Remove unecessory data

prashantbaste — Wed, 15 Jul 2015 10:05:23 GMT

Tick ✓ to 'Omit if value is Null' option for coloumn 'Name' if its a tablebox (Properties -> Presentation Tab).

Else if its a Pivot Table/Straight Table - Tick ✓ to 'suppress when value is null' option.

Hope this will resolve your problem.

Regards,

Prashant P Baste

Re: Remove unecessory data

awhitfield — Wed, 15 Jul 2015 10:10:20 GMT

Hi Muhammad<

1. Change to a Straight Table Dimension = Workorder1 and Name

2. Flag Name Dim - 'Suppress When When is Null'

3. Set the Expression = Sum(pay)

4. Presentation tab - Suppress Zero values and Suppress Missing both ticked

HHT - Andy

See the attached

Re: Remove unecessory data

sunny_talwar — Wed, 15 Jul 2015 10:10:39 GMT

Try this script:

discipline:

LOAD * inline

[

Workorder1,Name

1,Muhammad

2,James

3,Katie

4,Martin

];

GL:

LOAD * inline

[

Workorder, Pay

a1, 10

a1, 50

a2, 10

a2, 40

a2, 10

a3, 10

a4, 15

a5, 79

a5, 65

a9, 46

];

GL1:

NoConcatenate

Load Right(Workorder,1) as Workorder,

Sum(Pay) as Pay

Resident GL

Where Workorder <> 'a4'

Group By Workorder;

DROP Table GL;

GL2:

NoConcatenate

Load Workorder as Workorder2,

Pay

Resident GL1

Where Exists(Workorder1, Workorder);

DROP Table GL1;

GL3:

NoConcatenate

LOAD Workorder1 as Workorder2,

Name

Resident discipline

Where Exists (Workorder2, Workorder1);

DROP Table discipline;

Re: Remove unecessory data

krishna20 — Wed, 15 Jul 2015 10:11:45 GMT

Hi,

Try as in the image.You will get desired result.

Re: Remove unecessory data

qlikmsg4u — Wed, 15 Jul 2015 10:15:04 GMT

!Then Find the attached example !