topic Re: Help in Scripting in QlikView

Help in Scripting

Mon, 27 Apr 2015 10:43:06 GMT

Hi All,

I have a table as shown below:

Item	Header 2	Header 3
A	Mixed	C1
A	Mixed	C2
B	Mixed	C1
C	Mixed	C2

I wish to create the below table:

Item	Header 2	Test
A	Mixed	Y
B	Mixed	N
C	Mixed	N

Thanks,

Asma

Re: Help in Scripting

awhitfield — Mon, 27 Apr 2015 10:45:14 GMT

HI Asma,

what does the TEST signify?

Andy

Re: Help in Scripting

Mon, 27 Apr 2015 10:51:12 GMT

Test is a new column which would be created based on the values present in Header3. If a particular value in Item column has both C1 and C2 values in Header3, then the value should be marked as Y in Test column

Re: Help in Scripting

amit_saini — Mon, 27 Apr 2015 10:51:58 GMT

Hi Asma,

What is your Test condition???

Thanks,

Re: Help in Scripting

jagan — Mon, 27 Apr 2015 10:55:28 GMT

Hi,

Data:

LOAD

FROM DataSource;

LEFT JOIN(Data)

LOAD

Item,

Header2,

If(Count(Item) > 1, 'Y', 'N') AS Test

RESIDENT Data;

Regards,

Jagan.

Re: Help in Scripting

Mon, 27 Apr 2015 12:45:35 GMT

Looking your tables, I think you want this...

=if(Item ='A','Y','N')

What's your test condition?

Re: Help in Scripting

anbu1984 — Mon, 27 Apr 2015 12:51:24 GMT

Check replies in your previous post

Re: Help in Scripting

qlikviewwizard — Mon, 27 Apr 2015 16:01:19 GMT

Hi Jagan,

Used the same script but getting below error.

Invalid expression

left join ( Data )

LOAD

Item,

[Header 2],

If(Count(Item)>1, 'Y', 'N') as Test

RESIDENT Data

Please advise.

Re: Help in Scripting

maxgro — Mon, 27 Apr 2015 18:32:05 GMT

s1:

LOAD Item, [Header 2], [Header 3]

FROM

[https://community.qlik.com/thread/161786]

(html, codepage is 1252, embedded labels, table is @1);

s2:

load Item, [Header 2],

if(Test='C1-C2','Y','N') as Test;

load Item, [Header 2],

Concat(DISTINCT [Header 3], '-', [Header 3]) as Test

Resident s1

group by Item, [Header 2];

Re: Help in Scripting

ramoncova06 — Mon, 27 Apr 2015 19:04:27 GMT

you can also do this with a preceding load

FinalResult:

Load

Item,

[Header 2]

if(HeaderCount) > 1 ,'Y', 'N') as Test;

load

count([Header 3]) as HeaderCount,

Item,

[Header 2]

group by

Item,

[Header 2];

load

Item,

[Header 2],

[Header 3]

from table .....;

Re: Help in Scripting

jagan — Wed, 29 Apr 2015 05:11:02 GMT

Hi,

I forgot giving the group by in Load statement

left join ( Data )

LOAD

Item,

[Header 2],

If(Count(Item)>1, 'Y', 'N') as Test

RESIDENT Data

GROUP BY Item, [Header 2];