Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
jagannalla
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

10 Replies
jagannalla
Partner - Specialist III
Partner - Specialist III

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
Author

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
Author

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
Partner - Specialist III
Partner - Specialist III

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
Author

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
Specialist II
Specialist II

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
Partner - Specialist III
Partner - Specialist III

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
Author

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
Author


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