topic Re: Logic Help in QlikView

Logic Help

abhaysingh — Wed, 03 Jun 2015 09:12:43 GMT

Hi all,

i have one requirement can any one help me in this?

Header 1	Header 2	Header 3
A	1
B	2
C	3
D	4
E	5
F	6

Output should come in table like

Header 1	Header 2
ABC	6
DEF	15

A+B+C = 6

D+E+F=15

how i can do this?

thanks in advance

Re: Logic Help

alexandros17 — Wed, 03 Jun 2015 09:22:13 GMT

What is the logic?

You have to sum the first and the second 3 element or you have to sum Always A,B C then D E F?

In this case use a straight table

add a computed dimension:

If([Header1]='A' or [Header1]='B' or [Header1]='C', 'ABC', 'DEF')

Add a new expression:

Sum([Header 2])

Re: Logic Help

awhitfield — Wed, 03 Jun 2015 09:26:06 GMT

Hi Abhay,

created a calculated dimension as follows:

=if(match(Header1,'A','B','C'),'ABC',
if(match(Header1,'D','E','F'),'DEF'))

See example attached

HTH Andy

Re: Logic Help

PabloOrtiz — Wed, 03 Jun 2015 09:36:24 GMT

hi,

create a computed dimension like this

=IF([Header 1]<'D','ABC','DEF')

and an expression like this

sum([Header2])

Re: Logic Help

abhaysingh — Wed, 03 Jun 2015 09:37:34 GMT

hi Alessandro,,,

thanks for revert but this is something Static Solution... Not Dynamic.. what if my Data will Append??

suppose xyz value would come..than it will not work

Re: Logic Help

abhaysingh — Wed, 03 Jun 2015 09:38:04 GMT

Hi thanks for Revert but i am looking for dynamic solution

Re: Logic Help

PabloOrtiz — Wed, 03 Jun 2015 09:39:21 GMT

Example

Re: Logic Help

CELAMBARASAN — Wed, 03 Jun 2015 09:46:54 GMT

Create a Grouping based on the Criteria you want to combine.

Can you explain on what basis you are combining A, B and C?

Re: Logic Help

abhaysingh — Wed, 03 Jun 2015 09:51:02 GMT

Hi Celambarasan...

Suppose i have 100 value in header1 field than i want to group them according to first 3 letter would be ABC, than next three would be Def and so on

Re: Logic Help

alexandros17 — Wed, 03 Jun 2015 09:54:48 GMT

Simply add this dimension and expression:

If(Left([Header1],1)='A' or Left([Header1],1)='B' or Left([Header1],1)='C', 'ABC', 'DEF')

Add a new expression:

Sum([Header 2])

let me know

Re: Logic Help

abhaysingh — Wed, 03 Jun 2015 09:59:18 GMT

Giving me static value of ABC and for DEF and value of GHI added into DEF only..

i have added GHI value as appended Data..

Looking for Dynamic Solution For this

Re: Logic Help

Wed, 03 Jun 2015 10:12:29 GMT

Hi Abhay,

Have a look at the attached qvw.

Hope this helps.

Regards,

Jemimah

Re: Logic Help

CELAMBARASAN — Wed, 03 Jun 2015 10:15:48 GMT

May be it could help

Test:

LOAD Ceil(RowNo()/3) AS Group,* INLINE [

Header 1, Header 2

A, 1,

B, 2,

C, 3,

D, 4,

E, 5,

F, 6

];

Tab:

LOAD Group, Concat([Header 1]) AS Header, Sum([Header 2]) AS Total

Resident Test

Group By Group;

Drop Table Test;

Re: Logic Help

Wed, 03 Jun 2015 10:34:19 GMT

Try this. It could help.

test:

load * inline

[

H1, H2

a, 1

b, 2

C, 3

d, 4

e, 5

f, 6

g, 7

h, 8

i, 9

];

Left Join(test)

LOAD H1, FieldValue('H1', RecNo() - Mod(RecNo() - 1, 3)) & FieldValue('H1', RecNo()+1 - Mod(RecNo() - 1, 3)) &FieldValue('H1', RecNo()+2 -Mod(RecNo() - 1, 3)) as NewH1

Resident

test;

LOAD NewH1, Sum(H2) as NewH2

Resident test

Group by NewH1;

Refer attached file.