Concatenate data of single field

[#1]

Hi All,

I have a question regarding scripting.. I do not know how to implement this one on qlikview..

I have 2 tables:

table1:

Field1	Field2	KEY	CODE
abc	abc	XYZ	X
abc	abc	XYZ	Y
abc	abc	XYZ	Z
abc	abc	ABCD	A
abc	abc	ABCD	B
abc	abc	ABCD	C
abc	abc	TST	T
abc	abc	TST	S

table2:

KEY	CODE1	CODE2	CODE3	CODE4
XYZ	X	Y	Z
ABCD	A	B	C	D
TST	T	S

What I want to happen is, I want to create a flag telling me that the data on table1 is complete.

for example:

KEY XYZ, codes are: X,Y,Z which is present in table2 (CODE1, CODE2, CODE3)

now for KEY ABCD, only codes: A,B, and C is present which makes its flag to incomplete.

As for KEY TST, the codes T and S is both present in table which makes its flag to complete.

How can I achieve this? How can I concatenate the CODE field in table1 to match both tables?

Regards,

Lyn

[jagannalla · Accepted solution]

Hi,

Please check this,

T1:

LOAD * INLINE [

    Field1, Field2, KEY, CODE

    abc, abc, XYZ, X

    abc, abc, XYZ, Y

    abc, abc, XYZ, Z

    abc, abc, ABCD, A

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, TST, T

    abc, abc, TST, S

];

T2:

LOAD * INLINE [

    KEY, CODE1, CODE2, CODE3, CODE4

    XYZ, X, Y, Z,

    ABCD, A, B, C, D

    TST, T, S,

];

T3:

CrossTable(Dim,Val)

LOAD * Resident T2;

T4:

LOAD KEY,Concat(Val) as Con Resident T3 Group By KEY;

Join

LOAD KEY,Concat(CODE) as Con1 Resident T1 Group By KEY;

LOAD KEY,If(Con=Con1,1,0) as Flag Resident T4;

DROP Table T3,T4;

Cheers!!

Jagan

[#3]

Hi Jagan!

thanks for your reply! very nice approach using crosstable thanks! I will try to apply this with real data. Again, thank you

-Lyn

[#4]

Hi Jagan!

I have an additional question. What if my data is like this:

T1:

LOAD * INLINE [

    Field1, Field2, KEY, CODE

    abc, abc, XYZ, X

    abc, abc, XYZ, Y

    abc, abc, XYZ, Z

    abc, abc, ABCD, A

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, TST, T

    abc, abc, TST, S

    abc, abc, TST, S

    abc, abc, TST, S

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, ABCD, B

    abc, abc, ABCD, C

];

[jagannalla]

Hi,

use Distinct in Concat statement.

eg:

T4:

LOAD KEY,Concat(DISTINCT Val) as Con Resident T3 Group By KEY

;

Join

LOAD KEY,Concat(DISTINCT CODE) as Con1 Resident T1 Group By KEY;

Cheers!!

Jagan

[#6]

Hi Jagan,

Again thanks! I have another question though, what if my data is like this:

T1:

LOAD * INLINE [

    Field1, Field2, KEY, CODE

    abc, abc, XYZ, X

    abc, abc, XYZ, Y

    abc, abc, XYZ, Z

    abc, abc, ABCD, A

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, TST, T

    abc, abc, TST, S

    abc, abc, TST, S

    abc, abc, TST, S

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, ABCD, O

    abc, abc, ABCD, N/A

What if the CODE is not available in T2?

Thanks,

Lyn

[preminqlik]

hi may be like this  and find attachment

T1:

Load *,

KEY&'-'&CODE as COMPARISIONKEY;

LOAD * INLINE [

    Field1, Field2, KEY, CODE

    abc, abc, XYZ, X

    abc, abc, XYZ, Y

    abc, abc, XYZ, Z

    abc, abc, ABCD, A

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, TST, T

    abc, abc, TST, S

];

T2:

Load *,

KEY&'-'&T2.CODE as T2.COMPARISIONKEY,

if(Exists(COMPARISIONKEY,KEY&'-'&T2.CODE),0,1) as T2.NUMFLAG,

if(Exists(COMPARISIONKEY,KEY&'-'&T2.CODE),'Complete','Incomplete') as T2.COMPLETE_INCOMPLETEFLAG;

LOAD *,

SubField(CODEFIELD,'!~') as T2.CODE;

Load *,

KEY as T2.KEY,

rowno() as ROWNO,

CODE1&'!~'&CODE2&'!~'&CODE3&'!~'&CODE4 as CODEFIELD;

LOAD * INLINE [

    KEY, CODE1, CODE2, CODE3, CODE4

    XYZ, X, Y, Z,

    ABCD, A, B, C, D

    TST, T, S,

];

OVERALLSTATUS_MAP:

mapping  Load distinct KEY,

if(FINALKEY>0,'InComplete','Complete') as T1.TOTAL;

Load KEY,

sum(T2.NUMFLAG) as FINALKEY

Resident T2 where T2.CODE<>'' Group by KEY;

INDIVIDUALSTATUS_MAP:

Mapping load T2.COMPARISIONKEY,

T2.COMPLETE_INCOMPLETEFLAG as T1.INDIVIDUAL

Resident T2;

FINALT1:

Load *,

ApplyMap('INDIVIDUALSTATUS_MAP',COMPARISIONKEY,'Others') as T1.INDIVIDUAL,

ApplyMap('OVERALLSTATUS_MAP',KEY,null()) as T1.OVERALLSTATUS

Resident T1;

DROP Table T1;

INCOMPLETECODES:

LOad KEY,

T2.CODE as INCOMPLETECODES

Resident T2 where T2.CODE <>'' and  T2.COMPLETE_INCOMPLETEFLAG='Incomplete';

[jagannalla]

Hi Lyn,

Sorry I didn't get what you said. Can you please let me know with sample data what you want exactly.

Thanks,

Jagan

[#9]

Hi Jagan,

In our T2 table list the codes that are to be looked up.

Let's say that my T1 table consists of the ff. data:

T1:

LOAD * INLINE [

    Field1, Field2, KEY, CODE

    abc, abc, XYZ, X

    abc, abc, XYZ, Y

    abc, abc, TST, S

    abc, abc, ABCD, B

    abc, abc, ABCD, C

    abc, abc, ABCD, O

    abc, abc, ABCD, N/A

for our T2:

T2:

LOAD * INLINE [

    KEY, CODE1, CODE2, CODE3, CODE4

    XYZ, X, Y, Z,

    ABCD, A, B, C, D

    TST, T, S,

];

As you can see, there are CODES in T1 that is not available in T2. (CODES: O and N/A)

[#10]

You can also try this:

Table:

Load * inline [

...

...

];

Table_2:

noconcatenate

load *,

       len(KEY) as size

resident Table;

drop table Table;

left join(Table_2)

load KEY,

      count(distinct CODE) as attemps

resident Table_2;

Table_3

noconcatenate

load *,

      IF(size = attemps, 'complete',incomplete') as FLAG

resident Table_2;

drop Table_2

regards,

Marcelo