topic Re: Need help on a simple straight table in QlikView

Need help on a simple straight table

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

Hi Team,

I have a simple straight table to display. However the catch is I need to group the columns based on certain criteria.

My data looks like:

Month	Area	Number
Jan	A: Pass	100
Jan	A: Fail	10
Jan	A: No Run	20
Jan	B: Pass	200
Jan	B: Fail	10
Jan	B: No Run	50

What I want is:

	A			B
Month	A: Pass	A: Fail	A: No Run	B: Pass	B: Fail	B: No Run
Jan	100	10	20	200	10	50

The need is to add an additional row which shows the grouping A and B.

Can you please guide me how is this possible.

Please note that I need a Straight table and not a Pivot table

Thanks

Re: Need help on a simple straight table

Brett_Bleess — Mon, 06 Jan 2020 20:27:26 GMT

The only way I see of accomplishing this is to actually load the A and B data into separate fields in the load script, so you can separate things that way, I believe that is the only way this will make it possible to do what you want in a straight table chart...

Regards,
Brett

Re: Need help on a simple straight table

sayadutt — Mon, 06 Jan 2020 20:52:09 GMT

Hi BrettBleess,

I have loaded A and B separately in the load script, now the problem is how I can have the A and B displayed on the top row (highlighted in red) for Straight Table

	A			B
Month	A: Pass	A: Fail	A: No Run	B: Pass	B: Fail	B: No Run
Jan	100	10	20	200	10	50

Data loaded now:

Month	Parent	Area	Number
Jan	A	A: Pass	100
Jan	A	A: Fail	10
Jan	A	A: No Run	20
Jan	B	B: Pass	200
Jan	B	B: Fail	10
Jan	B	B: No Run	50

Re: Need help on a simple straight table

Brett_Bleess — Mon, 06 Jan 2020 21:12:02 GMT

You misunderstood where I was going with things! 🙂 You need a Parent A and Parent B field, separate field for each of those... Hopefully that makes things click, only way I see to do what you want to do, which may make the data model a bit ugly, but I think it should work.

Regards,
Brett

Re: Need help on a simple straight table

sayadutt — Mon, 06 Jan 2020 22:23:51 GMT

Hi, I am not sue if I understand this properly.

Is it possible for you to give a simply dummy example.

Thanks

Re: Need help on a simple straight table

rwunderlich — Tue, 07 Jan 2020 01:45:05 GMT

Make a Pivot Table with Month, Parent, Area as Dimensions. Sum(Number) as Expression.

Drag Parent & Area to the upper horizontal.

Check "Always Fully Expanded" in presentation.

See attached example qvw.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: Need help on a simple straight table

sayadutt — Tue, 07 Jan 2020 19:36:05 GMT

Hi rwunderlich,

The problem I am having now is I guess the cartesian product join.

I am loading data from 2 tables - one from excel and another inline.

Excel data:

Load

Month,

A_Pass,

A_Fail,

B_Pass,

B_Fail

from <excel file>

Inline Data:

Load * inline
[
Area, Parent
A_Pass, A
A_Fail, A
B_Pass, B
B_Fail, B
];

This is what I am getting if I create a pivot table

This is what I want, Parents A and B should be grouped properly.

Re: Need help on a simple straight table

rwunderlich — Wed, 08 Jan 2020 05:16:48 GMT

In your previous posts you showed a field named "Area" with values "A: Pass", "A: Norun", etc. And a field named "Number" that held the value. Your last posted script showed loading fields named "A_Pass", "A_Fail" etc and no Number field. So which is your data structure? Can you post a sample data file and/or qvw?

-Rob