8 Replies Latest reply: Jan 10, 2018 7:55 AM by Mohammed Mukram Ali RSS

    Joining two tables

    Swati Jha

      Hi guys

       

      I m loading two tables using "web file" connection method in Data load editor.

      Capture.JPG

       

      When I show them in table it comes like this ... If you can see one record is getting repeated ...number of times....Can some híne help solve this issue.

      Capture.JPG

        • Re: Joining two tables
          omar bensalem

          It's normal no?

          if each ConfluencePort has many HelpSitePorts coming from multiple sources? No?

            • Re: Joining two tables
              Swati Jha

              Do you see the difference .... if I import them different tables it doesnt get repeated .... but get repeated if kept in one table ....how do I fix this?

              Capture.JPG

                • Re: Joining two tables
                  omar bensalem

                  That's also normal;

                  for instance:

                  HelpSitePort = 80 is repeated but that normal; why?

                   

                  because we have HelpSitePort = 80 linked to ConfulencePort =80

                  and

                  HelpSitePort = 80 linked to ConfulencePort =443

                  Capture.PNG


                  so when u just put as a list selection, the HelpSitePort  field; Qlik will show the disctinct existing values;

                  but when u put it in a table with the ConfulencePort , Qlik will link each combination of ConfulencePort -HelpSitePort  .


                  Hope this was clear !



                   

                    • Re: Joining two tables
                      Swati Jha

                      yes ,... I get it now ..but to get my app working ..I need  to check the confluence ports and Helpsite ports how do I do that ...

                       

                      My do ..something like this .. If there is a port in confluence it should also be present in helspite port ..if it is not then I need to show it as a n alert

                        • Re: Joining two tables
                          omar bensalem

                          let me try to understand;

                           

                          if u have :

                          confluence ports  ,  Helpsite ports

                          80, 80

                          80 , 444

                          443,80

                          443, 443

                          200, 80


                          then u have a problem in the confulence Port 200; because u don't have Helpsite port =200 for the confluence one that is = 200

                          right?

                            • Re: Joining two tables
                              Swati Jha

                              Right ..this I need to show in table for others to understand

                                • Re: Joining two tables
                                  omar bensalem

                                  when u join all u table, the resulting one would be one final table;

                                  now try to adapt this to ur script:

                                   

                                   

                                   

                                  YourTable:

                                   

                                  load *, if(confluencePorts=HelpsitePorts,1,0) as Flag Inline [

                                  confluencePorts  ,  HelpsitePorts

                                   

                                  80, 80

                                  80 , 444

                                  443,80

                                  443, 443

                                  200, 80

                                  ];

                                   

                                   

                                  Test:

                                  Load confluencePorts AS Alert_confluencePorts where Flag=0;

                                  Load confluencePorts, sum(Flag) as Flag Resident YourTable  Group by confluencePorts;

                                   

                                   

                                  drop Field Flag From YourTable;


                                  result:

                                  Capture.PNG