19 Replies Latest reply: Aug 27, 2015 1:18 PM by Neena Bhattarai RSS

    I am having an issue with Joins.

    Neena Bhattarai

      I did not write this script below. Someone before me who is not here anymore wrote it and I am not familiar with joins or writing scripts like this one below. The issue that I am experiencing is that some data from the file F41092 is missing. This client has items that are either sold at an A price or a B price. But for some reason it isn't pulling in all the data. Only some B and A prices are displaying along with only one type of Product Code. Any help is much appreciated.

       

      TempValues:

       

      Load Distinct

      T2ITM AS [Short Name],

      T2ITM & '_' & T2EFT & '_'& T2EFTE AS [Temp Key],

      IF(T2KY='PRDCODE',T2RMK) AS [Product Code],

      IF(T2KY='AHASLPRC',T2AMTU)/100 AS [A Sell Price],

      IF(T2KY='AHAESCAMT',T2AMTU)/100 AS [A Escrow Amount],

      IF(T2KY='BNSLPRC',T2AMTU)/100 AS [B Sell Price],

      IF(T2KY='BNESCAMT',T2AMTU)/100 AS [B Escrow Amount],

      IF(T2EFT <>0,Date(MakeDate(If(len(T2EFT)>5, 1900+ left(T2EFT ,3), 1900+left(T2EFT ,2))) + (right(T2EFT,3)-1))) as [Effective Date],

      IF(T2EFTE <>0,Date(MakeDate(If(len(T2EFTE)>5, 1900+ left(T2EFTE ,3), 1900+left(T2EFTE ,2))) + (right(T2EFTE,3)-1))) as [Ending Date]

        ;

       

      SQL SELECT T2KY, T2ITM, T2AMTU, T2EFT, T2EFTE, T2RMK

      FROM $(....)F41092

      ;

       

       

      ItemValues:

      LOAD

        [Temp Key],

        [Short Name],

        [A Sell Price],

        [Effective Date],

        [Ending Date]

        Resident TempValues;

        Where not IsNull([A Sell Price]);

       

        Left Join (ItemValues)

      Load

        [Temp Key],

        Money([A Escrow Amount]) AS [A Escrow Amount]

        Resident TempValues

        Where not IsNull([A Escrow Amount])

        ;

       

       

      Left Join (ItemValues)

      Load

      [Temp Key],

      Money([B Sell Price]) AS [B Sell Price]

      Resident TempValues

      Where not IsNull([B Sell Price])

      ;

       

       

       

      Left Join (ItemValues)

      Load

      [Temp Key],

        Money([B Escrow Amount]) AS [B Escrow Amount]

        Resident TempValues

        Where not IsNull([B Escrow Amount])

        ;

       

       

      Left Join (ItemValues)

      Load

        [Temp Key],

        [Product Code] AS [Product Code]

        Resident TempValues

        Where not IsNull([Product Code])

        ;

       

      Drop Table TempValues;

       

      Left Join (SalesDetails)

        IntervalMatch([Order Date],[Short Name]) LOAD [Effective Date], [Ending Date],[Short Name] Resident ItemValues;

       

       

      LEFT JOIN (SalesDetails) LOAD

        *

        Resident ItemValues;

       

      Drop Table ItemValues;

       

      SchoolDates:

      Load

        DTDCTO & '_' & DTDOCO & '_' & DTKCOO as SalesHeaderKey,

        IF(DTEXD2 <>0,Date(MakeDate(If(len(DTEXD2)>5, 1900+ left(DTEXD2 ,3), 1900+left(DTEXD2 ,2))) + (right(DTEXD2,3)-1))) as [Extra Date 2]

      ;

      SQL SELECT DTDCTO, DTDOCO, DTKCOO, DTEXD2

      FROM $(.....)F8301DT

      WHERE DTKCOO='$(CompanyLimit)' AND DTEXD2>0

      ;

        • Re: I am having an issue with Joins.
          Sasidhar Parupudi

          Please load your application

          • Re: I am having an issue with Joins.
            Michalina Kuczynska

            Can you check if the missing items from F41092 have selling price A or B?

             

            If they have price B -> you will not bring them across to ItemValues table -> they will not be taken in the first resident load you are performing as they will not have price A assigned. Then in the next step when you are performing resident load where selling price B is not null -> you will find those elements there, but they will not be left joined into Item Values table (as left join between these tables will take only the rows from Table B that already exist in Table A).

             

            Maybe you should think about replacing

            Left Join (ItemValues)

            Load

            [Temp Key],

            Money([B Sell Price]) AS [B Sell Price]

            Resident TempValues

            Where not IsNull([B Sell Price])

            ;

             

             

            with

            Concatenate

            Load

            [Temp Key],

            Money([B Sell Price]) AS [B Sell Price]

            Resident TempValues

            Where not IsNull([B Sell Price])

            ;

             

            But as the person before me mentioned - can you upload your script?

              • Re: I am having an issue with Joins.
                Neena Bhattarai

                Thank you for the reply! Unfortunately, I cannot load a sample of my application. I looked in my datasouce and the items that should have either A prices or B prices or both are there... it isn't missing so something is wrong with my script. I can pull in just the first table (TempValues) without any joins and everything does display however, for each 2nd item number (short name) the price, escrow amount, and product code appear on different lines. I have included an example of one item number with multiple lines.

                Example  1.PNG

                 

                I want the table to look like this:

                Example 2.PNG

                  • Re: I am having an issue with Joins.
                    Michalina Kuczynska

                    Did you try to replace that left join with outer join? That should help you to bring across info about both prices - A and B. So it should look like the script in the attached txt. Let me know if it's any better now. Good luck!

                     

                    Edit - I'm starting to think that OUTER JOIN is what you are looking for, not concatenate, apologies for the confusion.

                      • Re: I am having an issue with Joins.
                        Neena Bhattarai

                        Thanks! I tried it out and it is getting closer to what I want however now the item number is displaying if it has both A AND B prices but what it is not displaying when the item number just has B prices and no A prices. Should I do an outer join for each price, escrow amount, and product code?

                          • Re: I am having an issue with Joins.
                            Michalina Kuczynska

                            It's really hard to determine without the sample app I'm afraid.

                             

                            What table of these 4 is the biggest one?

                              • Re: I am having an issue with Joins.
                                Neena Bhattarai

                                Well they are all coming from the same table within a file from oracle. When I bring it over to qlik each item number displays their price, escrow, and product code on different lines.

                                  • Re: I am having an issue with Joins.
                                    Sasidhar Parupudi

                                    please try  the bold parts in your script

                                     

                                    TempValues:

                                     

                                    Load Distinct

                                    T2ITM AS [Short Name],

                                    T2ITM & '_' & T2EFT & '_'& T2EFTE AS [Temp Key],

                                    IF(T2KY='PRDCODE',T2RMK) AS [Product Code],

                                    IF(T2KY='AHASLPRC',T2AMTU)/100 AS [A Sell Price],

                                    IF(T2KY='AHAESCAMT',T2AMTU)/100 AS [A Escrow Amount],

                                    IF(T2KY='BNSLPRC',T2AMTU)/100 AS [B Sell Price],

                                    IF(T2KY='BNESCAMT',T2AMTU)/100 AS [B Escrow Amount],

                                    IF(T2EFT <>0,Date(MakeDate(If(len(T2EFT)>5, 1900+ left(T2EFT ,3), 1900+left(T2EFT ,2))) + (right(T2EFT,3)-1))) as [Effective Date],

                                    IF(T2EFTE <>0,Date(MakeDate(If(len(T2EFTE)>5, 1900+ left(T2EFTE ,3), 1900+left(T2EFTE ,2))) + (right(T2EFTE,3)-1))) as [Ending Date]

                                      ;

                                     

                                    SQL SELECT T2KY, T2ITM, T2AMTU, T2EFT, T2EFTE, T2RMK

                                    FROM $(....)F41092

                                    ;

                                     

                                    NoConcatenate

                                     

                                    ItemValues:

                                    LOAD

                                      [Temp Key],

                                      [Short Name],

                                      [A Sell Price],

                                      [Effective Date],

                                      [Ending Date]

                                      Resident TempValues;

                                      Where not IsNull([A Sell Price]);

                                     

                                      Left Join (ItemValues)

                                    Load

                                      [Temp Key],

                                      Money([A Escrow Amount]) AS [A Escrow Amount]

                                      Resident TempValues

                                      Where not IsNull([A Escrow Amount])

                                      ;

                                     

                                     

                                    Left Join (ItemValues)

                                    Load

                                    [Temp Key],

                                    Money([B Sell Price]) AS [B Sell Price]

                                    Resident TempValues

                                    Where not IsNull([B Sell Price])

                                    ;

                                     

                                     

                                     

                                    Left Join (ItemValues)

                                    Load

                                    [Temp Key],

                                      Money([B Escrow Amount]) AS [B Escrow Amount]

                                      Resident TempValues

                                      Where not IsNull([B Escrow Amount])

                                      ;

                                     

                                     

                                    Left Join (ItemValues)

                                    Load

                                      [Temp Key],

                                      [Product Code] AS [Product Code]

                                      Resident TempValues

                                      Where not IsNull([Product Code])

                                      ;

                                     

                                    Drop Table TempValues;

                                     

                                    Left Join (SalesDetails)

                                      IntervalMatch([Order Date],[Short Name]) LOAD [Effective Date], [Ending Date],[Short Name] Resident ItemValues;

                                     

                                     

                                    LEFT JOIN (SalesDetails)

                                    LOAD

                                      *

                                      Resident ItemValues;

                                     

                                    Drop Table ItemValues;

                                    NoConcatenate

                                    SchoolDates:

                                    Load

                                      DTDCTO & '_' & DTDOCO & '_' & DTKCOO as SalesHeaderKey,

                                      IF(DTEXD2 <>0,Date(MakeDate(If(len(DTEXD2)>5, 1900+ left(DTEXD2 ,3), 1900+left(DTEXD2 ,2))) + (right(DTEXD2,3)-1))) as [Extra Date 2]

                                    ;

                                    SQL SELECT DTDCTO, DTDOCO, DTKCOO, DTEXD2

                                    FROM $(.....)F8301DT

                                    WHERE DTKCOO='$(CompanyLimit)' AND DTEXD2>0

                                    ;

                                • Re: I am having an issue with Joins.
                                  Michalina Kuczynska

                                  Not sure if that will be of any help to you, but see the qvw - maybe ApplyMap would be the most sufficient way to bring across some of the columns from different tables?

                          • Re: I am having an issue with Joins.
                            arjun rao

                            Could you attach the sample application?

                            • Re: I am having an issue with Joins.
                              Neena Bhattarai

                              The data is coming from Oracle it is not an inline load. I cannot load a sample application so that above is my example of what happens when the data is loaded in with no joins at all and the table right below it is what I want.