topic Re: Shw in QlikView

Shw

rkspareek1992 — Fri, 31 Aug 2018 09:05:06 GMT

Hi,

I have many data tables in below-mentioned tables (here I only showed two tables for example). Table1 consist of Production & recovery data and table2 consist of Sales data. Now My requirement is to show the data in Row-wise format as per below mentioned desired outputs. Output can be only dimension wise (as per output1) or It can be Plant or material wise (as per output2).

Kindly suggest how can I get the desired output.

Table1				Table2
Plant	Material	Production	Recovery	Plant	Material	Sales
1001	10001	10	20	1001	10001	10
1002	10001	20	30	1002	10001	20
1003	10001	30	40	1003	10001	30
1001	10002	40	10	1001	10002	40
1002	10002	50	30	1002	10002	50
1003	10002	60	40	1003	10002	60
1001	10003	70	40	1001	10003	70
1002	10003	80	30	1002	10003	80
1003	10003	90	40	1003	10003	90

Table1				Table2
Plant	Material	Production	Recovery	Plant	Material	Sales
1001	10001	10	20	1001	10001	10
1002	10001	20	30	1002	10001	20
1003	10001	30	40	1003	10001	30
1001	10002	40	10	1001	10002	40
1002	10002	50	30	1002	10002	50
1003	10002	60	40	1003	10002	60
1001	10003	70	40	1001	10003	70
1002	10003	80	30	1002	10003	80
1003	10003	90	40	1003	10003	90

Output1
Sales	450
Production	450
Recovery	280

Output2
Dimension	Plant	Total
Sales	1001	120
Sales	1002	150
Sales	1003	180
Production	1001	120
Production	1002	150
Production	1003	180
Recovery	1001	70
Recovery	1002	90
Recovery	1003	120

Re: Shw

arvind1494 — Fri, 31 Aug 2018 10:40:32 GMT

Hi,

Use following script for your required output:

Table1:

load * Inline

[Plant,Material,Production,Recovery,

1001,10001,10,20

1002,10001,20,30

1003,10001,30,40

1001,10002,40,10

1002,10002,50,30

1003,10002,60,40

1001,10003,70,40

1002,10003,80,30

1003,10003,90,40

];

Table2:

LOAD * Inline

[Plant,Material,Sales

1001,10001,10

1002,10001,20

1003,10001,30

1001,10002,40

1002,10002,50

1003,10002,60

1001,10003,70

1002,10003,80

1003,10003,90

];

Sal:

load Plant, sum(Sales) as Total_Sales Resident Table2 Group by Plant;

output_data :

load 'Sales' as Dimension ,Plant,Total_Sales as Total Resident Sal;

drop table Sal;

production_d:

load Plant,sum(Production) as Total_Prodution Resident Table1 Group by Plant;

load 'Production' as Dimension,Plant,Total_Prodution as Total Resident production_d;

drop table production_d;

Recovery_d:

load Plant,sum(Recovery) as Total_Recovery Resident Table1 Group by Plant;

load 'Recovery' as Dimension,Plant,Total_Recovery as Total Resident Recovery_d;

drop table Recovery_d;

drop tables Table1,Table2;

Re: Shw

arvind1494 — Fri, 31 Aug 2018 10:46:57 GMT

If you need OUTPUT then add following line at the end of above script:

Ouput1:

load Dimension, sum(Total) as Output1_Total Resident output_data Group by Dimension ;

Re: Shw

rkspareek1992 — Fri, 31 Aug 2018 11:01:42 GMT

Thanks, Arvind for your valuable time, I don't wanna use sum or group by functions. Because I have already mentioned that these 2 tables are the only example and every table consist of more than 10 dimensions. I have more than 20 transactions like the above example tables. I want to show the data on the front end.

Kindly suggest query for the front end to show the data.

Re: Shw

arvind1494 — Fri, 31 Aug 2018 11:36:17 GMT

THEN IT IS VERY SIMPLE USE PICK MATCH FUNCTIONS IN CHART

Re: Shw

rkspareek1992 — Tue, 04 Sep 2018 06:17:30 GMT

Did you mean I have to user Pick Match in expression?

Can you please suggest how to use?

Re: Shw

gawalimegha — Tue, 04 Sep 2018 07:25:10 GMT

Hello Rakesh,

Please find .qvw contains solution to give output1.

This can be achieved by using crossTable.

Code:

Table1:

Crosstable (Measures, Sales, 2)

load * Inline

[Plant,Material,Production,Recovery,

1001,10001,10,20

1002,10001,20,30

1003,10001,30,40

1001,10002,40,10

1002,10002,50,30

1003,10002,60,40

1001,10003,70,40

1002,10003,80,30

1003,10003,90,40

];

Table2:

Crosstable (Measures, Sales, 2)

LOAD * Inline

[Plant,Material,Sales

1001,10001,10

1002,10001,20

1003,10001,30

1001,10002,40

1002,10002,50

1003,10002,60

1001,10003,70

1002,10003,80

1003,10003,90

];

LOAD Measures, sum(Sales)

Resident Table1

Group by Measures;

drop Table Table1;

Re: Shw

gawalimegha — Tue, 04 Sep 2018 07:30:31 GMT

Here is output2:

Re: Shw

rkspareek1992 — Tue, 04 Sep 2018 07:51:51 GMT

Hi Megha,

Thanks for your time & reply...

But I cannot use crosstable, because I have more than 20 tables like this, which are concatenated. So there I cannot user cross table or group by function.

I just want to show the data in the output format. Kindly suggest any format through the front end.

Re: Shw

arvind1494 — Tue, 04 Sep 2018 07:59:15 GMT

Tell me how many distinct values are their in Measue

like Production,Sales,Recovery

Re: Shw

chinnuchinni — Tue, 04 Sep 2018 09:48:32 GMT

Hi Rakesh,

For output 1:

take pivot table without dimension. and expressions are

Sales: sum(Sales)

Production: sum(Production)

Recovery: sum(Recovery)

For output2:

dimensions: valuelist('Sales','Production','Recovery') , plant

expression : sum(Sales)

Re: Shw

gawalimegha — Tue, 04 Sep 2018 10:33:41 GMT

Hello Rakesh,

For Output 1 you can use straight table:

1)without any dimensions. Expressions as sum([Production]), etc.

2) In presentation tab check Horizontal column.

Re: Shw

rkspareek1992 — Tue, 04 Sep 2018 11:18:05 GMT

Hi,

I am using Qlik sense... Kindly suggest the solution in Qliksense.

Re: Shw

arvind1494 — Tue, 04 Sep 2018 11:46:52 GMT

Hi Rakesh,

Please download the attachment and try to understand.

topic Re: Shw in QlikView

Shw

Output2

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw

Re: Shw