topic Re: Issue at script level in QlikView

Issue at script level

vipin_mishra479 — Mon, 19 Oct 2015 06:28:08 GMT

Hi All,

I have 1 table where field like this

Entity Id	Result	Entity	SEQNO
E123	Failed	A	1
E123	Failed	B	2
E123	Failed	C	3
E123	Failed	D	4
E123	Pass	E	5
E123	Pass	F	6
E123	Failed	G	7
E123	Failed	H	8
E123	Failed	I	9

for this table i want only failed result SEQNO and store that SEQNO in 1 variable

like vVariable = '1','2','3','4','7','8','9'

than i want pass that variable value in another table

like where wildmatch(SEQNO,$(vVariable))

Please suggest me how can i store that variable in script level and pass that variable in script level

Re: Issue at script level

phaneendra_kunc — Mon, 19 Oct 2015 06:32:27 GMT

Try these....

Solution 1 - Variable

TableXYZ:

Load Concat(Distinct SEQNO,',') as Var_SEQNO From TableXYZ Where Result = 'Failed';

Let vVariable = Peek('Var_SEQNO',0,TableXYZ);

Solution 2 - Where Exists

Failed_SEQ:

Load SEQNO as FailedSEQ From TableXYZ Where Result = 'Failed'; //======Either this can be FROM or RESIDENT

While loading another table use "Where Exists"

TablePQR:

Load * From TablePQR where exists (FailedSEQ,SEQNO);

Re: Issue at script level

jagan — Mon, 19 Oct 2015 06:39:52 GMT

Hi,

Try like this

Data:

LOAD

Concat(Distinct SEQNO,',') as FailedSeqNo

FROM DataSource

WHERE Result = 'Failed';

Let vFailedSeqNo = Peek('FailedSeqNo',0,Data);

DROP TABLE Data;

Hope this helps you.

Regards,

Jagan.

Re: Issue at script level

jcoggon — Mon, 19 Oct 2015 06:46:32 GMT

Hi Vipin,

Phaneendra's solution would work with a few tweaks as below:

TABLE1:

LOAD

[Entity Id],

Result,

Entity,

SEQNO

FROM TABLE1

ConcatTable:

NoConcatenate

LOAD Distinct Concat(SEQNO,',') as Var_SEQNO

Resident TABLE1

WHERE Result = 'Failed';

Let vVariable = FieldValue('Var_SEQNO',1);

DROP TABLE ConcatTable;

TABLE2:

NoConcatenate

LOAD *

Resident TABLE1

WHERE wildmatch(SEQNO,$(vVariable));

DROP TABLE TABLE1;

Re: Issue at script level

Mon, 19 Oct 2015 06:50:00 GMT

Main:

LOAD [Entity Id],

Result,

Entity,

SEQNO

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

LOAD Concat(SEQNO,',') as seq Resident Main Where Result='Failed';

let vSeq = peek('seq',0,Temp);

EXIT Script;

Re: Issue at script level

vipin_mishra479 — Mon, 19 Oct 2015 07:01:37 GMT

Thanks to all its working fine

Re: Issue at script level

neha_sri — Mon, 19 Oct 2015 07:29:22 GMT

Hi Vipin,

I think you can calculate the seq no at backend and then use it.

Load

[Entity Id],

     Result,

     Entity,

     SEQNO,

     if(Result='Failed',SEQNO)   as Updated_seq

Regards,

Neha