8 Replies Latest reply: Oct 11, 2015 2:42 PM by Sunny Talwar RSS

    Filtering Data

    Matt Melhus

      I have a very large data set and I'm trying to filter out the data I don't need during the load process.  I've been able to filter Material using the Where Not Exists() below, but I still need to filter further.

       

      In the MainTable the Material column contains a value called Service Contract  and an Order column that contains a contact number value (ex. 400234234).   I've been able to pull a list of all contract values (Order column) that I need to keep (as outlined in IncludedFile below).  Now I'm trying filter out all rows that contain a contract values that are not included in the IncludedFile where the Material is equal to Service Contract.

       

      I'm thinking that I need to do some sort of combine of Material and Order in the IncludedContracts table to create a unique key and then use a Where Exists() but I can't seem to get that to work. (items commented out won't work but show thought process).

       

      I'm pretty new to Qlik so any thoughts or suggestions would be greatly appreciated.

       

      ExcludeMaterial:

      LOAD

      Material as ExcludedMaterial

      FROM ExcludedFile;

       

      NoConcatenate

      IncludedContracts:

      LOAD

      Material,

      "Order",

      //Material&"Order" as ContractKey

      FROM IncludedFile;

       

      NoConcatenate

      MainTable:

      LOAD Material,

                "Orders",

                AllOtherColumns

      FROM MainFile

      Where Not Exists (ExcludedMaterial,Material);

      //Where Exists (ContractKey,Material&"Orders");


      DROP Table ExcludeMaterial;

      DROP Table IncludedContracts;




        • Re: Filtering Data
          Sunny Talwar

          If I understand your requirement, may be this:

           

          ExcludeMaterial:

          LOAD

          Material as ExcludedMaterial

          FROM ExcludedFile;

           

          TempTable:

          LOAD Material,

                    Orders,

                    AllOtherColumns

          FROM MainFile

          Where Not Exists (ExcludedMaterial,Material);

           

          IncludedContracts:

          LOAD Orders as IncludeOrders

          FROM IncludedFile;

           

          NoConcatenate

          MainTable:

          LOAD Material,

                    Orders,

                    AllOtherColumns

          Resident TempTable

          Where Exists (IncludeOrders, Orders) and Material = 'Service Contract';


          Concatenate (MainTable)

          LOAD Material,

                    Orders,

                    AllOtherColumns

          Resident TempTable

          Where not Material = 'Service Contract';


          DROP Tables ExcludeMaterial, IncludedContracts, TempTable;

            • Re: Filtering Data
              Matt Melhus

              I'm trying to get the data to load but keep getting the following error: Field not found - <Line Itm>. The error seems to be occurring in the section below. Thoughts?

               

              NoConcatenate
              MainTable:
              LOAD
                  Material,
                  Customer,
                  "Customer Name" as CustomerName,
                  "Sub Category 1" as SubCategory1,
                  "Sub Category 2" as SubCategory2,
                  "Order Type" as OrderType,
                  "Invoice Type" as InvoiceType,
                  "Sales Region" as SalesRegion,
                  "Order",
                  Reference
              Resident TempTable
              Where Exists (Includeorders,"Order") and Material='SERVICE CONTRACT';

                • Re: Filtering Data
                  Sunny Talwar

                  Can you post the complete script?

                    • Re: Filtering Data
                      Matt Melhus

                      Here it is...

                       

                      ExcludeMaterial:
                      LOAD
                          Material as Material_Excl
                      FROM [lib://ProductLine/Product Line (Software and Hardware)\ServiceException.xlsx]
                      (ooxml, embedded labels, table is Sheet1);


                      TempTable:
                      LOAD
                          Material,
                          Customer,
                          "Customer Name" as CustomerName,
                          "Sub Category 1" as SubCategory1,
                          "Sub Category 2" as SubCategory2,
                          "Order Type" as OrderType,
                          "Invoice Type" as InvoiceType,
                          "Sales Region" as SalesRegion,
                          "Order",
                          Reference
                      FROM [lib://ProductLine/testComputerService 2011 through Oct 8 2015.xlsx]
                      (ooxml, embedded labels, table is Sheet1)
                      WHERE NOT EXISTS (Material_Excl,Material)
                      ;

                       

                      NoConcatenate
                      IncludedContracts:
                      LOAD
                         "Order" as Includeorders
                         FROM [lib://ProductLine/Product Line (Software and Hardware)\SoftwareContractFilter.xlsx]
                      (ooxml, embedded labels, table is Sheet1);

                       

                      NoConcatenate
                      MainTable:
                      LOAD
                          Material,
                          Customer,
                          "Customer Name" as CustomerName,
                          "Sub Category 1" as SubCategory1,
                          "Sub Category 2" as SubCategory2,
                          "Order Type" as OrderType,
                          "Invoice Type" as InvoiceType,
                          "Sales Region" as SalesRegion,
                          "Order",
                          Reference
                      Resident TempTable
                      Where Exists (Includeorders,"Order") and Material='SERVICE CONTRACT';

                       

                      Concatenate(MainTable)
                      LOAD
                          Material,
                          Customer,
                          "Customer Name" as CustomerName,
                          "Sub Category 1" as SubCategory1,
                          "Sub Category 2" as SubCategory2,
                          "Order Type" as OrderType,
                          "Invoice Type" as InvoiceType,
                          "Sales Region" as SalesRegion,
                          "Order",
                          Reference
                      Resident TempTable
                      Where not Material='SERVICE CONTRACT';

                       

                      DROP Tables ExcludeMaterial,IncludedContracts,TempTable;

                        • Re: Filtering Data
                          Sunny Talwar

                          Try this:

                           

                          ExcludeMaterial:
                          LOAD
                              Material as Material_Excl
                          FROM [lib://ProductLine/Product Line (Software and Hardware)\ServiceException.xlsx]
                          (ooxml, embedded labels, table is Sheet1);


                          TempTable:
                          LOAD
                              Material,
                              Customer,
                              "Customer Name" as CustomerName,
                              "Sub Category 1" as SubCategory1,
                              "Sub Category 2" as SubCategory2,
                              "Order Type" as OrderType,
                              "Invoice Type" as InvoiceType,
                              "Sales Region" as SalesRegion,
                              "Order",
                              Reference
                          FROM [lib://ProductLine/testComputerService 2011 through Oct 8 2015.xlsx]
                          (ooxml, embedded labels, table is Sheet1)
                          WHERE NOT EXISTS (Material_Excl,Material);

                           

                          NoConcatenate
                          IncludedContracts:
                          LOAD
                            "Order" as Includeorders
                            FROM [lib://ProductLine/Product Line (Software and Hardware)\SoftwareContractFilter.xlsx]
                          (ooxml, embedded labels, table is Sheet1);

                           

                          NoConcatenate
                          MainTable:
                          LOAD
                              Material,
                              Customer,
                              CustomerName,
                              SubCategory1,
                              SubCategory2,
                              OrderType,
                              InvoiceType,
                              SalesRegion,
                              "Order",
                              Reference
                          Resident TempTable
                          Where Exists (Includeorders,"Order") and Material='SERVICE CONTRACT';

                           

                          Concatenate(MainTable)
                          LOAD
                              Material,
                              Customer,
                              CustomerName,
                              SubCategory1,
                              SubCategory2,
                              OrderType,
                              InvoiceType,
                              SalesRegion,
                              "Order",
                              Reference
                          Resident TempTable
                          Where not Material='SERVICE CONTRACT';

                           

                          DROP Tables ExcludeMaterial,IncludedContracts,TempTable;

                           

                          Since you are using a resident load from TempTable, you need to use their new names instead of the old ones.

                           

                          Best,

                          Sunny

                          • Re: Filtering Data
                            Sunny Talwar

                            Is it working now?