8 Replies Latest reply: Dec 27, 2011 4:14 AM by Sokkorn Cheav RSS

    Help on Set Analysis

      Dear All,

       

      Any one please help on set analysis, I can found that what I am doing wrong.

       

      please help, attaching a sample here

       

      thanks

       

      anil

        • Help on Set Analysis

          Check date format on vEndDt and docDate so you don't compare apples with pears.

            • Help on Set Analysis

              thanks to both of you for reply,

               

              but can u explain more what you are saying and can u correct me in my attached QV example what I am doing wrong.

               

              Thanks & Regards

               

              anil

                • Help on Set Analysis
                  FRANCISCO Orte López

                  Hi Anilsknp ,

                   

                  I just copy your load script and just paint , in bold and underlined , what you need to change in order to solve your problem ...

                   

                   

                  Party:
                  LOAD iPartyId,
                  sPartyName,
                  SalesRep,
                  sPartyGroupName,
                  unit1,
                  unit2,
                  unit3,
                  sml,
                  prtZone;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQVBasicSalesParty;

                   

                  SML:
                  LOAD iPartyId,
                  SML;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQVBasicSalesPartyTonsSML;

                   

                  Machine:
                  LOAD macId,
                  sMachineNo,
                  sMacTypeName,
                  sPaperTypeName,
                  iTPD,
                  iTPM,
                  iTPY,
                  posId,
                  sMacPositionName,
                  sMacSubPositionName,
                  iOMacSpeedTo,
                  iInvLength,
                  iInvWidth;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQVBasicSalesMachineDet;

                   

                  ProductGroup:
                  LOAD iItemGroupId,
                  sItemGroupName,
                  grpName;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQVBasicSalesProductGroups;

                   

                   

                   

                  FinYear:
                  LOAD

                  Date(docDate) as docDate,
                  DayNo,
                  month(docDate) as month,
                  MonthName(docDate) as mntName,
                  mntNo,
                  Quarter,
                  curQuarter,
                  fyYear1,
                  fyYear2,
                  fyDate1,
                  fyDate2,
                  curFyDate1,
                  curFyDate2,
                  nxtFyDate1,
                  nxtFyDate2,
                  lst1Date,
                  lst2Date,
                  lst3Date,
                  lst4Date,
                  lst5Date,
                  lst1FyDate1,
                  lst1FyDate2,
                  lst2FyDate1,
                  lst2FyDate2,
                  lst3FyDate1,
                  lst3FyDate2,
                  lst4FyDate1,
                  lst4FyDate2,
                  lst5FyDate1,
                  lst5FyDate2,
                  lst5FinYear,
                  lst4FinYear,
                  lst3FinYear,
                  lst2FinYear,
                  lst1FinYear,
                  "Financial Year",
                  curFinYear,
                  nxtFinYear;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQvFinanceCurNxtLstFinYear;

                   

                   

                   

                  MaxSalDate:
                  LOAD "Financial Year",
                  fyMaxSalTrnDate;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQvFinMaxSalTrnDate;

                   

                  Finance:
                  LOAD trnType,
                  docId,
                  docNo,
                  Date(docDate) as docDate,

                  sourceNo,
                  sourceDate,
                  iSrNo,
                  iPartyId,
                  iPymtCommitDays,
                  iMaxPymtCommitDays,
                  iItemGroupId,
                  sItemName,
                  iWireNo,
                  posId,
                  iLength,
                  iWidth,
                  Sqmtr,
                  iAgrPrice,
                  iAgrRate,
                  iInvoiceAmountINR,
                  iExport,
                  sPymtModeName,
                  sDocMediaName,
                  invGrTotal,
                  collAmt,
                  crnAmt,
                  outStndAmt,
                  outStndAgeToDays,
                  recdOn,
                  recdInDays;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQvFinanceZZZFinal;

                   

                  InvDet:
                  LOAD sInvNo,
                  Date(docDate) as docDate,

                  invAmt,
                  dVchDate,
                  iAmount;
                  SQL SELECT *
                  FROM ERPDATA.dbo.viewQvFinanceInvDetail;

                   

                   

                  Regards

                  • Help on Set Analysis
                    Sridhar Ethiraj

                    Your date formate in Docdate and Variable are in different formates.

                     

                    Variable $(vEndDt) Returns - 4.8098505739755e-005

                     

                    Docdate has MM/DD/YYYY hh:mm:ss TT formate.

                     

                    if i properly understood your requirement, You need to create you Docdate with out timestampe and equate same with variable $(vEndDt)

                     

                    Hope i`m clear.

                     

                    -Sridhar

                • Help on Set Analysis
                  FRANCISCO Orte López

                  Hi Anilsknp ,

                   

                  As Thomas said, try cheking date format for docdate field on your load script ( just using date(docdate) as docdate each time yo load docdate on your script) and it will work correctly.

                   

                  Hope it helps ,

                   

                  Regards

                  • Re: Help on Set Analysis
                    Sokkorn Cheav

                    Hi Anil,

                     

                    Two thing to do:

                    1. Change variable vEndDt = DATE(monthend(max(docDate)) ,'M/D/YYYY hh:mm:ss TT')

                    2. Change expression =max(vEndDt) to =vEndDt

                     

                    See the sample attached file also.

                     

                    Let me know if this one help you.

                     

                    Regards,

                    Sokkorn Cheav