7 Replies Latest reply: Mar 20, 2014 11:45 AM by Herbert Chitate RSS

    Join

      Hi All,

       

      I have data in two tables that I would like to incorporate into one table.

       

      The first table is a customer order table at individual order level (customers can have multiple orders)with Customer ID and Quantity ordered columns ( as well as a lot of other columns). The second table is a Customer despatches table at individual order despatch level. This has customer ID and and quantity despatched columns ( plus other columns).

       

      I now need to create a new table at customer ID level which will have the columns Customer ID , Quantity Ordered, Quantity Despatched.

       

      How can I go about achieving this.

       

      All help appreciated

       

      Herbert

        • Re: Join
          Ankit Mishra

          Is there no column in any of the two table like order number which marks a particular order uniquely?

          • Re: Join
            Gysbert Wassenaar

            Usually it's enough to create a straight table with Customer ID as dimension and two expressions: sum([Quantity ordered]) and sum([Quantity Dispatched]). If you want to calculate it in the script you can do something like this:

             

            SummaryTable:

            Load [Customer ID], sum([Quantity ordered]) as [Quantity Ordered Customer Total]

            Resident Orders

            group by [Customer ID];

             

            join (SummaryTable)

            Load [Customer ID], sum([Quantity Dispatched]) as [Quantity Dispatched Customer Total]

            Resident Dispatches

            group by [Customer ID];

              • Re: Join

                Thanks Gysbert,

                 

                I am getting a syntax, which says "missing/misplaced FROM: SummaryTable:..." I have converted your code to:

                 

                SummaryTable:

                LOAD TIMB,

                Sum([Bales Loaded]),as [Total Loaded]

                 

                Resident [Bale Loop]

                 

                group by TIMB;

                 

                Thanks Herbert

                  • Re: Join
                    Gysbert Wassenaar

                    You have a comma just before the as that shouldn't be there: Sum([Bales Loaded]),as [Total Loaded]

                      • Re: Join

                        Fantastic Gysbert,

                         

                        The join worked perfectly. I have added a bit more to the code to say Total Loaded - Total Delivered.

                         

                        For some strange reason, the calculation is working fine where there is a value in both columns Total Loaded and Total Delivered. Where there is a value in only one of the columns, it is not performing the calculation. The code now looks like this:

                         

                         

                        SummaryTable1:

                        LOAD TIMB,

                        Sum([Bales Loaded])as [Total Loaded]

                         

                        Resident [BALE LOOP]

                         

                        group by TIMB;

                         

                        Join(SummaryTable1)

                        LOAD TIMB,

                        Sum([Bales Delivered]) as [Total Deliveries]

                        Resident BOOKINGS

                        Group by TIMB;

                         

                        [SUMMARYTABLE]:

                        NoConcatenate LOAD

                            TIMB,

                            if([Total Loaded]='',0,[Total Loaded]) as [Total Loaded],

                            if([Total Deliveries]='',0,[Total Deliveries]) as [Total Deliveries],

                            [Total Loaded]-[Total Deliveries]

                            

                            Resident [SummaryTable1]

                            Order by TIMB;

                            

                            DROP Table [SummaryTable1];

                         

                         

                        Please help.

                         

                        Thanks

                          • Re: Join
                            Gysbert Wassenaar

                            I think in this case what you can use is the rangesum function, which can deal with nulls.

                             

                            [SUMMARYTABLE]:

                            NoConcatenate LOAD

                                TIMB,

                                [Total Loaded],

                                [Total Deliveries],

                                rangesum([Total Loaded],-[Total Deliveries]) as Result   

                                Resident [SummaryTable1]

                                Order by TIMB;

                             

                            If you want to test for null values (or blanks) use if(len(trim(SomeField))=0, ...

                              • Re: Join

                                Gysbert,

                                 

                                You have a very good grasp of QlikView, many thanks for your help. As we would say in this part of the world, You do not deserve to walk amongst mere men.

                                 

                                How long have you been using the product and do you have any tips for someone wanting to learn a whole lot more.

                                 

                                Thank you

                                 

                                Herbert