Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
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.
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;
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;
Thanks to all its working fine
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