9 Replies Latest reply: Jul 27, 2010 6:05 PM by Anil Konduri RSS

    plz help in data model

    Anil Konduri

      Hi,

      I have two tables

      Table 1

      -------------------

      Acct No

      Date

      xxx

      xxx

      xxx

       

      Table 2

      ----------------

      Acct No

      Date

      Revenue

      Buy/Sell

      xxx

      xxx

      xxx

      When I join using Acct no revenue data is not showing properly, the problem is with date... how to I join these two tables so that I can use a list box for date, & other columns to filter.

       

      Thanks in Advance

        • plz help in data model

          May be there wud be some account no repeating in table 1 that's why the revenue number wudn't be matching, you need to try distinct account no for revenue expression.

          Shumail

          • plz help in data model

            Is the Date column in Table 1 the same as the Date column in Table 2? if so drop the Date field when importing Table 2

            OR

            What way are you joing the data, left join, concatenate....

            If left Join

            Table_1:

            LOAD

            Acct No, Date;

            SQL Select * From tableA;

            LEFT JOIN ([Table_1])

            LOAD

            Acct No, Date, Revenue, Buy/Sell;

            SQL Select * From tableB;

            OR Try

            Table_1_Temp:

            LOAD

            Acct No, Date;

            SQL Select * From tableA;

            Table_1:

            CONCATENATE LOAD

            Acct No, Date, Revenue, Buy/Sell;

            SQL Select * From tableB;

            NOCONCATENATE LOAD

            Acct No, Date, Revenue, Buy/Sell

            RESIDENT Table_1_Temp;

            DROP TABLE Table_1_Temp;

             

              • plz help in data model
                Anil Konduri

                both are different dates

                  • plz help in data model

                    QV is joining the tables based on both of the fields that have identical names, which is not what you want.

                    If the dates are different, as you mention, alias one, or both of your date fields to stop them from joining (and creating a synthetic key). I would recomend giving them a more meaningful name than 'date'. Describe what each of the date fields are actually for. This will solve the issue of the bad join, and make it easier to report on these dates.

                     

                • plz help in data model
                  Anil Konduri

                  let me give an example for my requirement

                  Example Table 1:


                  Table 1



                  dateAcct NoAcct TypeCompany NameTicketsProd Type
                  7/26/20101000RetailABC Ltd20aaa
                  7/27/20101000RetailABC Ltd10bbb
                  7/26/201015000MarketXYZ LTD

                  1

                  5

                  aaa

                  Example Table 2:


                  Table 2


                  DateAcct NoBuy/SellRevenue
                  7/26/20101000Buy500
                  7/26/20101000Sell250
                  7/27/20101000Buy200
                  7/27/201015000Buy500

                   

                   

                   

                  • plz help in data model
                    Anil Konduri

                    let me give an example

                    Example Table 1:


                    Table 1



                    dateAcct NoAcct TypeCompany NameTicketsProd Type
                    7/26/20101000RetailABC Ltd20aaa
                    7/27/20101000RetailABC Ltd10bbb
                    7/26/201015000MarketXYZ LTD

                    15

                     

                    aaa

                    Example Table 2:


                    Table 2


                    DateAcct NoBuy/SellRevenue
                    7/26/20101000Buy500
                    7/26/20101000Sell250
                    7/27/20101000Buy200
                    7/27/201015000Buy500

                     

                     

                     

                    • plz help in data model
                      Anil Konduri

                      Any Help?

                        • plz help in data model

                          As I suggested earlier, if your fields, both labled 'date' represent different dates, then they must be aliased to prevent Qlikview from joining them together.

                          When Qlikview sees two fields that are called the same thing in two different tables, it assumes that this a join condition. If you actually intend to join the records ONLY on account ID, then these date fields must be given different names in your load script. (date AS [Some Other Name])

                        • plz help in data model
                          Anil Konduri

                          Thanks for your help...but that is not my question, I should be able to filter my data using "Date" filed. How to get the common date field as because i have two date fields if i use table 1 date the revenue date wont changes, & if i use table 2 date then the tickets wont changes