Left join in edit script

Thu, 25 Aug 2011 06:39:38 GMT

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

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;

thanks in advance

Regards

Venkat

Re: Left join in edit script

Thu, 25 Aug 2011 07:21:57 GMT

Hi,

Please go through the attached file ...

this may help you...

thanks,

Niranjan

topic Left join in edit script in QlikView

Left join in edit script

Re: Left join in edit script