Left join in edit script

[#1]

Hi all,

Here in the below edit script code , facing an problem with creating the Left join with Summarized Table.

Can anyone look over this and suggest me where i had done mistake,

Here Lotkey contains B004567, B002343, B243324, ... so on. (These all are repeats more than once with same name)

Now  based on this individual LotKey Value the Min, Max and Avg should be calculate.

Ex :

LotKey,  vSplit

  B004567, 12

B004567, 23

B004567, 57

Result:

       Min(vSplit) = 12

       Max(vSplit) = 57

In the same way for  B002343,B243324 and so on.. also,

Summarized:
LOAD 
        LensKey,
        BaseCurve,
        Brand,
        LotNo,
        LotStartDateTime,
        SpherePower,
        CaptureDateTime,
        PalletStatusCode,
//        HydStatusCodeA,
        DefectName,
        DefectStatus,
        LensResult,

     $(vProcessVariables)
//     if(DefectName ='Defect.EdgeChip',DefectName) as CountDefectEdgeChip,
//     if(DefectName ='Defect.EdgeTear',DefectName) as CountDefectEdgeTear,
//     if(DefectName ='Defect.ExcessPiece.Flash',DefectName) as CountDefectExcessPieceFlash,
//     if(DefectName ='Defect.ExcessPiece.ExcessPoly', DefectName) as CountDefectExcessPieceExcessPoly,
//     if(DefectName ='Defect.Bubble.Single',DefectName) as CountDefectBubbleSingle
    
    
FROM
[..\2_QVDGenerator\QVD\2005.qvd]
(qvd) ;

FOR i = 1 to SubStringCount(KeepChar('$(vSummDefects)',','),',')+1
LET vName=SubField('$(vSummDefects)',',',$(i));
LET vSplit = SubField(vSummDefects,',',$(i));

All:
LOAD
FieldValue('$(vName)', IterNo()) as $(vName),
FieldValue('LensKey', IterNo()) as LensKey
AutoGenerate(1)
While not IsNull (FieldValue('$(vName)', IterNo()));
Left Join (Summarized)
LOAD
LensKey,
Min($(vSplit)) as Min$(vName),
Max($(vSplit)) as Max$(vName),
Avg($(vSplit)) as Avg$(vName)
Resident All;
DROP Table All;
NEXT

Summarized:
LOAD 
        LensKey,        
        Brand,          
     $(vProcessVariables)

    
FROM
[..\2_QVDGenerator\QVD\2005.qvd]
(qvd) ;

FOR i = 1 to SubStringCount(KeepChar('$(vSummDefects)',','),',')+1
LET vName=SubField('$(vSummDefects)',',',$(i));
LET vSplit = SubField(vSummDefects,',',$(i));

All:
LOAD
FieldValue('$(vName)', IterNo()) as $(vName),
FieldValue('LensKey', IterNo()) as LensKey
AutoGenerate(1)
While not IsNull (FieldValue('$(vName)', IterNo()));
Left Join (Summarized)
LOAD
LensKey,
Min($(vSplit)) as Min$(vName),
Max($(vSplit)) as Max$(vName),
Avg($(vSplit)) as Avg$(vName)
Resident All;
DROP Table All;
NEXT

Summarized:
LOAD 
        LotKey,        
        Brand,          
     $(vProcessVariables)

    
FROM
[..\2_QVDGenerator\QVD\2005.qvd]
(qvd) ;

FOR i = 1 to SubStringCount(KeepChar('$(vSummDefects)',','),',')+1
LET vName=SubField('$(vSummDefects)',',',$(i));
LET vSplit = SubField(vSummDefects,',',$(i))

All:
LOAD
FieldValue('$(vName)', IterNo()) as $(vName),
FieldValue('LotKey', IterNo()) as LotKey

AutoGenerate(1)
While not IsNull (FieldValue('$(vName)', IterNo()));
Left Join (Summarized)  // Here left is not creating , and it is showing error field not found

LOAD
LotKey,
Min($(vSplit)) as Min$(vName),
Max($(vSplit)) as Max$(vName),
Avg($(vSplit)) as Avg$(vName)
Resident All;
DROP Table All;

NEXT

thanks in advance

Regards

Venkat

[#2]

Hi,

Please go through the attached file ...

this may help you...

thanks,

Niranjan