7 Replies Latest reply: Jan 13, 2014 3:48 PM by Srikanth P RSS

    WHERE.... IN......

    Robert Fishel

      Hello my fellow QlikView friends.  I am wondering how to approach this section of SQL code in QlikView.  I need to re-write it as a LOAD statement:

       

      WHERE (((asn_output_CN.Consign_number) In (SELECT [Consign_number] FROM [asn_output_CN] As Tmp GROUP BY [Consign_number] HAVING Count(*)>1 ))) ORDER BY asn_output_CN.Consign_number;

       

       

      Any thoughts??  Thank you 

        • Re: WHERE.... IN......

          I'd do it this way (maybe there's better though):

           

          table:

          load * from asn_output_CN.qvd (qvd);

           

          left join(table)

          load Consign_number, count(Consign_number) as nb_consign

          resident table

          group by Consign_number

          ;

           

          inner join(table)

          load distinct Consign_number

          resident table

          where nb_consign>1

          ;

           

          drop field nb_consign;

           

          Remains only the lines for which Consign_number has more than 1 entry in the original table.

          • Re: WHERE.... IN......
            Stefan Wühl

            Look into using WHERE EXISTS(Field, expression), i.e. first load your Consign number from asn_output_CN, then

             

            your second table

             

            LOAD ..

             

            FROM ...

            WHERE EXISTS([Conseign number], [asn_output_CN.Consign_number]);

              • Re: WHERE.... IN......
                Robert Fishel

                Like this, swuehl??

                 

                 

                table:

                LOAD    CONSIGN_NUMBER

                RESIDENT asn_output_CN;

                 

                LOAD   SERVICE,

                              MODE,

                              Cartons,

                              CBM,

                              UNITS,

                              ACTUAL_WT,

                              CHARGEABLE_WT,

                              TERMS,

                              ORIGIN,

                              COUNTRY_CODE,

                              COUNTRY,

                              SUMOFWAR_RISK,

                              SUMOFFUEL_SURCH,

                              SUMOFFREIGHT_CHARGES,

                              SUMOFFOB_PICKUP,

                              SUMOFOTHER_COSTS,

                              SUMOFAIRFREIGHT,

                              SUMOFEXTRA_DEPART,

                              SUMOFSEA_FREIGHT_95X_CAF,

                              SUMOFCUSTOMS_CLEARANCE,

                              SUMOFHANDLING_STA_TAXES,

                              SUMOFTRANSFER_APT_WH,

                              SUMOFSTORAGE_FEE,

                              SUMOFHANDLING_CHARGES,

                              SUMOFEXTRA_CUSTOM_CLEARANCE,

                              SUMOFDELIVERY_TO_CAMPEGINE,

                              SUMOFOTHERS,

                              SUMOFFREIGHT_COST,

                              SUMOFEXTRA_COST,

                              SUMOFTOTAL_COST

                FROM

                WHERE EXISTS(CONSIGN_NUMBER, asn_output_CN.Consign_number);

              • Re: WHERE.... IN......
                Srikanth P

                You can try like below: Lets assume data source are qvd's

                 

                TEMP_CONSIGNNO:

                LOAD DISTINCT Consign_number

                FROM [asn_output_CN.qvd] (qvd)

                Where Count(Consign_number) Group By Consign_number ;

                 

                TEMP_MAIN:

                LOAD * FROM SOURCEMAINQVD.qvd (qvd)

                Where Exists(Consign_number) ; // This statement only Consign_number exists in the TEMP_CONSIGNNO

                 

                DROP Table TEMP_CONSIGNNO ;

                 

                MAIN:

                NOCONCATENATE

                LOAD * RESIDENT TEMP_MAIN Order BY Consign_number ; // For Order by

                 

                DROP Table TEMP_MAIN ;

                  • Re: WHERE.... IN......
                    Srikanth P

                    If you have the Consign_number values list you can try with Match function

                     

                    Where Match(Consign_number , 100, 102, 140 ) ;

                    • Re: WHERE.... IN......
                      Robert Fishel

                      This is my actual SQL statement:

                       

                      SELECT asn_output_CN.Consign_number, asn_output_CN.Service, asn_output_CN.Mode, asn_output_CN.Cartons, asn_output_CN.CBM, asn_output_CN.Units, asn_output_CN.Actual_wt, asn_output_CN.Chargeable_wt, asn_output_CN.Terms, asn_output_CN.Origin, asn_output_CN.Country_code, asn_output_CN.Country, asn_output_CN.SumOfWar_risk, asn_output_CN.SumOfFuel_surch, asn_output_CN.SumOfFreight_charges, asn_output_CN.[SumOfFob-pick_up], asn_output_CN.SumOfOther_costs, asn_output_CN.SumOfAirfreight, asn_output_CN.SumOfextra_depart, asn_output_CN.SumOfSea_freight_95X_CAF, asn_output_CN.SumOfCustoms_clearance, asn_output_CN.[SumOfHandling-s-t-a_taxes], asn_output_CN.[SumOfTransfer_apt-wareho], asn_output_CN.SumOfStorage_fee, asn_output_CN.SumOfHandling_charges, asn_output_CN.SumOfExtra_custom_clearance, asn_output_CN.SumOfDelivery_to_Campegine, asn_output_CN.SumOfOthers, asn_output_CN.SumOffreight_cost, asn_output_CN.SumOfextra_cost, asn_output_CN.SumOfTotal_cost

                      FROM asn_output_CN

                      WHERE (((asn_output_CN.Consign_number) In (SELECT  FROM  As Tmp GROUP BY  HAVING Count(*)>1 )))

                      ORDER BY asn_output_CN.Consign_number;