Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Concatenate data of single field

Hi All,

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

I have 2 tables:

table1:

Field1Field2KEYCODE
abcabcXYZX
abcabcXYZY
abcabcXYZZ
abcabcABCDA
abcabcABCDB
abcabcABCDC
abcabcTSTT
abcabcTSTS

table2:

KEYCODE1CODE2CODE3CODE4
XYZXYZ
ABCDABCD
TSTTS

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

Tags (3)
1 Solution

Accepted Solutions
jagannalla
Valued Contributor III

Re: Concatenate data of single field

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

10 Replies
jagannalla
Valued Contributor III

Re: Concatenate data of single field

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

Not applicable

Re: Concatenate data of single field

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

Not applicable

Re: Concatenate data of single field

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
Valued Contributor III

Re: Re: Concatenate data of single field

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

Not applicable

Re: Re: Concatenate data of single field

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
Valued Contributor II

Re: Concatenate data of single field

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
Valued Contributor III

Re: Re: Re: Concatenate data of single field

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

Not applicable

Re: Re: Concatenate data of single field

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)

Not applicable

Re: Concatenate data of single field


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

Community Browser