Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
HI Asma,
what does the TEST signify?
Andy
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
Hi Asma,
What is your Test condition???
Thanks,
AS
Hi,
Data:
LOAD
*
FROM DataSource;
LEFT JOIN(Data)
LOAD
Item,
Header2,
If(Count(Item) > 1, 'Y', 'N') AS Test
RESIDENT Data;
Regards,
Jagan.
Looking your tables, I think you want this...
=if(Item ='A','Y','N')
What's your test condition?
Check replies in your previous post
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.
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];
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 .....;