topic Re: Help in Scripting in QlikView

Help in Scripting

Wed, 22 Apr 2015 10:15:36 GMT

Hi All,

I have a table as shown below:

A B

AA test

AA sample

AB test

AC sample

Now I have to load only those values of A in another table where value of B is both test and sample. The resulting table would look something like shown below:

A B

AA final

AB test

AC sample

Thanks,

Asma

Re: Help in Scripting

ali_hijazi — Wed, 22 Apr 2015 10:19:13 GMT

since in the other table you'll have the same column names A, and B then auto concatenate will be applied by QlikView i.e. the two records will be added to original table

you can either do the following :

Table:

noConcatenate

load * resident original_table where B='test'

this way you'll have two tables but if you keep them you'll end up with synthetic keys

so you need to delete either of them

or you can do the following:

Table:

load A as Field1, B as Field2

resident original_table

where B='test'

Re: Help in Scripting

prieper — Wed, 22 Apr 2015 10:22:23 GMT

Think that the EXISTS-formula might be of help

Aircode

A: LOAD A FROM TableA WHERE MATCH(B, 'test', 'sample');

B: LOAD * FROM TableB WHERE EXISTS(A);

DROP TABLE A;

Peter

Re: Help in Scripting

Gysbert_Wassenaar — Wed, 22 Apr 2015 10:37:39 GMT

Maybe like this:

Temp:
LOAD * INLINE [
    A, B
    AA, test
    AA, sample
    AB, test
    AC, sample
];

Result:
NoConcatenate
LOAD A, 'Final' as B WHERE List = 'sample|test';
LOAD A, concat(B, '|', B) as List
RESIDENT Temp
GROUP BY A;

DROP TABLE Temp;

Re: Help in Scripting

anbu1984 — Wed, 22 Apr 2015 10:46:45 GMT

Load A Where B Like '*test*' And B Like '*sample*';
Load A,Concat(B,',') As B Group By A;
Load * Inline [
A,B
AA,test
AA,sample
AB,test
AC,sample ];