Issue at script level

[vipin_mishra479]

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

[phaneendra_kunc · Accepted solution]

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);

[jagan]

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.

[jcoggon]

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;

[#5]

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;

[vipin_mishra479]

Thanks to all its working fine

[neha_sri]

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