topic Re: the question is Region has its Subfields so the case study is as below in QlikView

the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 11:46:56 GMT

R1(Sb1,Sb2,Sb3)=data(1,5,7)

R2(Sb4)=data(9)

R3(Sb5)=data(10)

R4(Sb6,Sb7)=data(7,8)

R5(Sb8)=data(9)

Re: the question is Region has its Subfields so the case study is as below

SreeniJD — Fri, 09 Oct 2015 11:49:40 GMT

Hi Sumit

Can you please give some more clarity on your question

Sreeni

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 11:50:13 GMT

please rep me soon....!!!

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 11:53:34 GMT

i have 3 fields in my main table ie,region,sub_region and data,i want my result in the new field take it as dimension in that i want to show the total of data of sub_regions with the respective Regions.

Re: the question is Region has its Subfields so the case study is as below

SreeniJD — Fri, 09 Oct 2015 12:06:36 GMT

use the calculated dimension as =AGGR(sum(data),sub_region,region)

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 12:09:43 GMT

i want this output by using backend by using scripting not at frontend

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 12:12:36 GMT

tell me the same procedure to implement it on backend

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 12:18:41 GMT

I WANT OUTPUT LIKE THIS

dim Data

SB1 1

SB2 5

SB3 7

R1 13

SB4 9

R2 9

SB5 10

R3 10

SB6 7

SB7 8

R4 15

SB8 9

R5 9

Re: the question is Region has its Subfields so the case study is as below

sumitjadhav — Fri, 09 Oct 2015 13:05:23 GMT

i got the solution for the same please check it once

table1:

LOAD * INLINE [

Region, Sub_Region, data,code

R1, Sb1, 1,1

R1, Sb2, 5,1

R1, Sb3, 7,1

R2, Sb4, 9,2

R3, Sb5, 10,3

R4, Sb6, 7,4

R4, Sb7, 8,4

R5, Sb8, 9,5

];

new:

LOAD

//Region&'_'&Sub_Region as Dimension,

subfield(Region&'_'&Sub_Region,'_') as dim

Resident table1;

Re: the question is Region has its Subfields so the case study is as below

sudeepkm — Fri, 09 Oct 2015 13:13:52 GMT

I'm not sure If I understand your question. Are you trying to group the data by Region?

In that case you can use group by clause doing a resident from the existing table.

Re: the question is Region has its Subfields so the case study is as below

martynlloyd — Fri, 09 Oct 2015 13:29:04 GMT

Solution (but you should rethink your strategy )

DataIn:
LOAD * INLINE [
    Region, Sub_Region, data,code
    R1, Sb1, 1,1
    R1, Sb2, 5,1
    R1, Sb3, 7,1
    R2, Sb4, 9,2
    R3, Sb5, 10,3
    R4, Sb6, 7,4
    R4, Sb7, 8,4
    R5, Sb8, 9,5
   ];

NewTable:
LOAD
Region & Sub_Region as SortKey
,Sub_Region as Dim
,data
Resident DataIn
;
Concatenate
LOAD
Region & 'ZZZ' as SortKey
,Region as Dim
,Sum(data) as data
Resident DataIn
Group by Region
;

Re: the question is Region has its Subfields so the case study is as below

sasiparupudi1 — Fri, 09 Oct 2015 14:49:46 GMT

Please try

LOAD * INLINE [

Region, Sub_Region, data,code

R1, Sb1, 1,1

R1, Sb2, 5,1

R1, Sb3, 7,1

R2, Sb4, 9,2

R3, Sb5, 10,3

R4, Sb6, 7,4

R4, Sb7, 8,4

R5, Sb8, 9,5

];

NoConcatenate

Temp:

load Region,concat(Sub_Region,',') as xx,sum(data) as data Resident x Group by Region ;

NoConcatenate

region:

load Region,data Resident Temp;

NoConcatenate

subregions:

load subfield(xx,',') as Sub_Region Resident Temp;

left join(subregions)

load Region,Sub_Region, data as SubregionData Resident x;

drop Table x,Temp;

hth

Sasi