topic Re: Table Cal help??? in QlikView

Table Cal help???

amit_saini — Wed, 17 Dec 2014 08:38:59 GMT

Hi Folks ,

See below:

I'm using pivot table , from level1 to Level 5 name are my dimensions and Quantity and Price are expressions .

I'm simply calling Quantity and Price fields as expression .

Problem here is I'm getting duplicates numbers or values see below :

And If I'm calling only Level 1 and Level 1 Name as dimension and Quantity as expression I'm getting only single value .

My requirement here is to show all levels with there respective names as in image but Quantity and Price figures should not repeat.

Any Suggestion???

Thanks,
AS

Re: Table Cal help???

alexandros17 — Wed, 17 Dec 2014 08:42:19 GMT

Try with

Sum(price)

sum(qty)

as expression

let me know

Re: Table Cal help???

sujeetsingh — Wed, 17 Dec 2014 08:46:42 GMT

Confused ...can you post a sample or come in more words.

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 08:54:06 GMT

Hi Sujeet,

Please see the attachments.

Here for number "1024936001" in Quantity I'm getting duplicate values like 1700, 1700

Thanks,
AS

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 08:55:41 GMT

Also in my previous comment see the image , each number is having some relation on different level . See color combinations .

Thanks,

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 08:56:25 GMT

sorry not working.

Thanks,
AS

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 09:37:44 GMT

Hi Sujeet

Not sure if we can do this with Dimensinality() function. I tried but no results.

Thanks

Re: Table Cal help???

saumyashah90 — Wed, 17 Dec 2014 09:48:39 GMT

Hi Amit,

IT seems you have 1-N (one to many relation ship) i.e for Level1 you have 2or more values for other levels.

You will get two values if you take all levels as dimensions

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 10:02:32 GMT

Saumya,

Yes u are right I do have 1-N relations and that's why i m getting duplicates .

If I restrict this to only one level I'm getting perfect result but user want to see all levels with and I'm struggling here.

Thanks,

Re: Table Cal help???

saumyashah90 — Wed, 17 Dec 2014 10:06:47 GMT

Option1) Use Pivot Chart showing only Level1 and quantity.Once user clicks on + sign he can check other levels(but this will show duplicates in the end result)

Options2) You may have to change the script using GroupBy function

Option3)Try using other levels in expressions instead of Dimensions(this might get you null values)

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 10:10:21 GMT

Thanks Saumya but I have already tried all these suggestions .

Thanks,

Re: Table Cal help???

saumyashah90 — Wed, 17 Dec 2014 10:13:35 GMT

What kind of ouput you want?

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 10:17:27 GMT

Should be based on level 1 0nly but need to show all other levels too.

Re: Table Cal help???

saumyashah90 — Wed, 17 Dec 2014 10:18:58 GMT

I meant to ask...

1)You need only one row with all levels?

2)Both rows but result displayed only on one row?

Re: Table Cal help???

amit_saini — Wed, 17 Dec 2014 11:32:20 GMT

Only one row with all levels.

Thanks,
AS

Re: Table Cal help???

saumyashah90 — Wed, 17 Dec 2014 11:35:20 GMT

That should not be done anyways users want it so

For Level5 as Dimension -?Supress Null.. you will get your output

Re: Table Cal help???

jagan — Thu, 18 Dec 2014 05:50:37 GMT

HI Amit,

Try like this

Dimension: Level1, Level1 Name

Expression: Maxstring(Level2), Maxstring(Level2 Name),..............................

When you use MaxString it will pull only value even you have 1 to many values.

If this is not you are expression then come up sample output that you are expecting with this 1 to many data.

Regards,

Jagan.

Re: Table Cal help???

amit_saini — Thu, 18 Dec 2014 06:50:04 GMT

Sorry Jagan,

not working.

Please see the attachment for desired result.

Thanks,

Re: Table Cal help???

tyagishaila — Thu, 18 Dec 2014 07:56:31 GMT

Hi Amit,

You can create a customized table,

A list box for dimensions,

Level 1

Level 2

Level 3

Level 4

and when you select here a dim Level 1,Pivot table show Level 1 data (as per your desired result)

like wise select Level 2, it shows Level 2 data and so on.

Re: Table Cal help???

amit_saini — Thu, 18 Dec 2014 08:41:01 GMT

Thanks Shaila, I have already suggested this option to my client

But my user want to see this as I mentioned in my attachment.

Thanks,
AS