14 Replies Latest reply: Jul 10, 2017 6:14 AM by Ricky Tanna RSS

    Where Exists

      Hi Everyone,

       

      I have something to learn from you all.

      I have two qvds and- History & Present.

       

      In History we store the monthly snapshots and Present holds the current details.

      For the Product details we need to Join them with Product qvd.

      Now the requirement is to find the number of missing products from History to

      Present.

       

      There are many items which were in pending stage, and the related(corresponding) product

      numbers were stored in History qvd but with due course of time, those products got

      cancelled and hence later on the Present qvd we don't have those details.

       

      SO, now my task is to find how many of those products are missing.

      I guess we need to use WHERE Exists, can someone suggest me what should I do next.

       

      Thanks and Regards,

      Bikash

        • Where Exists
          Steve Dark

          If I follow your description correctly, you want to load the records from History where the products are missing so that you can re-instate them?

           

          The code will be something like:

           

          Temp_ProductID:

          LOAD

               ProductID

          FROM Product.qvd (qvd);

           

          History:

          LOAD
               *

          FROM History.qvd (qvd)

          WHERE NOT EXISTS (ProductID);

           

          DROP TABLE Temp_ProductID;

           

          You can then interrogate the loaded rows to try and work out what is missing.

           

          If I have misread your requirements then please post back with more details.

           

          - Steve

            • Where Exists

              Hi Steve,

              Thank you so much for the answer.

               

              Could you please tell how does it find the missing products, as because neither my History nor Present QVD has the field "Product ID". Its there in Product QVD. Product QVD has 11 fields and History & Present has the same 86 fields.

              Part ID, which is different from Product ID is common in these three QVDs.

              How do I join them first. Only then I guess I would be able to follow your steps.

               

               

              Thanks again

              Regards,

              Bikash

                • Where Exists
                  Steve Dark

                  Sorry, I missed the issue slightly.  It will be simpler to load all three QVDs (the two identical ones should concatenate) and then insert a List Box with the Part ID in it. From here enter the following search expression:

                   

                  =sum(if(isnull([Product ID]), 1, 0)) > 0

                   

                  You will need to ensure you delete the ** wildcard characters that appear by default on the search criteria.

                   

                  This should then select only rows where there is no Product ID.

                   

                  Alternatively I think that if you use the WHERE EXISTS syntax from above, but replace ProductID with [Part ID] it shold only load the rows with issues.

                   

                  Regards,

                  Steve

                    • Where Exists

                      Steve can you tell me how to write the code to concatenate.

                      Because both the identical QVDs has exactly the same field names.

                      will there be issues of synthetic key?

                       

                      Thanks & Regards,

                      Bikash

                        • Where Exists
                          Steve Dark

                          If you load exactly the same columns from both QVD's they will automatically concatenate.  It is always a good idea to do a Limited Load with just a few rows (found under Debug in the script editor) as QlikView can hang if you load two tables with almost the same columns.

                           

                          You can also force a concatenate by placing a CONCATENATE statement between the two loads.  If the columns are not exactly the same though a non-optimized QVD load will be performed.  This will be much slower.  Search the community for more info on optimized / non-optimized loads.

                           

                          Regards,
                          Steve

                            • Where Exists

                              Thanks Steve with your suggestion I can load both the identical QVDs History & Present.

                              But I have a Product qvd. First a left join has to be performed on History and Product QVDs

                              to get the product details with the History and then I also have to do a left join on Present

                              and Product QVD.

                               

                              Could you please tell me how do i do that with running into synthetic keys.

                              If I can achieve the above then I can perform the Where Exists clause that you suggested.

                               

                              Thanks again,

                              Bikash

                                • Where Exists
                                  Steve Dark

                                  Surely if both Present and History have loaded into a single table, and the only common key between those and the Product table is the [Part ID] then there would be no issue with synthetic keys?

                                   

                                  Could you perhaps do a Limited Load of 100 rows or so and then post up a screenshot of the resulting table structure?

                                   

                                  - Steve

                              • Where Exists

                                hi! try something like this...

                                 

                                LOAD

                                A & '-' & B AS AB,

                                A,

                                B,

                                C

                                from TABLE1;

                                 

                                LOAD

                                *

                                WHERE EXISTS(AB);

                                LOAD

                                A & '-' & B AS AB,

                                D,

                                E

                                from TABLE2;

                                 

                                I made this ... has someone found another solution?

                                probably have! because in this way i load 2 times the same table to get result.. so ... anybody????

                                 

                                thank you!!!

                                 

                                bikashdebnath ... I sincerely hope have helped you

                                 

                                André Mussi

                        • Where Exists
                          rohit gupta

                          hiii..

                          hw can i use Where clause in my script....plz give an detail example

                          • Where Exists
                            rohit gupta

                            hi...sir

                            thaanx for helping me ..i git ur attachment ..is helps me a lot.

                            now i understood hw to use.

                            • Re: Where Exists
                              Ricky Tanna

                              Hi

                               

                              It's great your question has been answered...I have created a white paper on this topic and hopefully it will help broaden individual's knowledge on the topic. There are multiple ways to limit the data load which are included.

                               

                              Ever have the issue of loading in data which does not really need to exist in your data model?

                               

                              Would you like to maybe load in new order details which aren’t linked to a previous order ID?

                               

                              Would you like to load in transactional data based on the data that already exists within your data model?

                               

                              Functions exist in the QlikView script which allows the user to force only certain values to be brought in/or left out. This powerful feature can support in controlling the amount of data your qvw is holding – helping in fine tuning the performance of your dashboard and productionising your application.

                               

                              Thanks,

                              Ricky