8 Replies Latest reply: Mar 10, 2013 3:00 AM by Ankri Laurent RSS

    Help : Two tables to one table

    Ankri Laurent

      Hello

       

      I have 2 tables :

      QV_RET_CHAMBRES_DISPO.QVD with the columns :

      RESIDENCE_NUM

      PRODUIT_NOM

      TARIF

      where there is several records per RESIDENCE_NUM

       

      QV_RET_DEPENDANCE_VW.QVD with the columns :

      RESIDENCE_NUM

      GIR_1_2

      GIR_3_4

      GIR_5_6

      where there is one record per RESIDENCE_NUM

       

      I want to load in QV a unique table with the columns :

      RESIDENCE_NUM

      PRODUIT_NOM

      TARIF

      TARIF_GIR = (TARIF+GIR_5_6)*30.5

       

      How do I have to write the sript for this ?

       

      Thanks a lot

       

      Laurent

        • Re: Help : Two tables to one table
          vishal waghole

          Hi

           

          Edit your Script like this and find attachmnet :

           

          QV_RET_DEPENDANCE_VW:

          LOAD * Inline [
          RESIDENCE_NUM,     GIR_1_2,     GIR_3_4,     GIR_5_6
          001,               111,          222,          333,
          002,               444,          555,          666]
          ;


          Join

          QV_RET_CHAMBRES_DISPO:
          LOAD * Inline [
          RESIDENCE_NUM,      PRODUIT_NOM,      TARIF
          001,               100,               1000,
          001,               200,               2000,
          001,               300,               3000,
          002,               200,               9000,
          002,               400,               2500
          ]
          ;


          FINAL_QV_RET_DEPENDANCE_VW:
          LOAD RESIDENCE_NUM,
          PRODUIT_NOM,
          TARIF,
          GIR_5_6,
          (
          TARIF + GIR_5_6)*30.5 as TARIF_GIR

          Resident QV_RET_DEPENDANCE_VW;


          DROP Table QV_RET_DEPENDANCE_VW;     

           

          Thanks

           

          Vishal

            • Re: Help : Two tables to one table
              Ankri Laurent

              Hi

              Thank you for your help

              I have tried to adapt your script (because I want the final table will be QV_RET_CHAMBRES_DISPO and not QV_RET_DEPENDANCE), but it doesn't work well

               

              I have attached my document in this message

               

              Could you help me to know why it does no work.

              I want to have finally 2 tables :

              QV_RET_RESIDENCES

              and

              QV_RET_CHAMBRES_DISPO_FINAL which is the concatenation of the 2 tables QV_RET_CHAMBRES_DISPO and QV_RET_DEPENDANCE

               

              Thanks a lot

               

              laurent

                • Re: Help : Two tables to one table
                  vishal waghole

                  Please try this one.

                  Here  i attached QVW file.

                   

                  Thanks

                  Vishal

                    • Re: Help : Two tables to one table
                      Ankri Laurent

                      Hi Vishal

                       

                      It desn't work

                      I have the message 'Syntax error : FROM missing....'

                       

                      Thanks

                        • Re: Help : Two tables to one table
                          vishal waghole

                          Hi,

                           

                          Yes Its exactly right.

                           

                          Syntax error because you joining two table but you didnt mentioned

                          from where you pick up DATA for table

                          QV_RET_CHAMBRES_DISPO  and

                          QV_RET_DEPENDANCE_VW .

                           

                          see..

                           

                          QV_RET_CHAMBRES_DISPO:
                          LOAD * Inline[
                          RESIDENCE_NUM,
                          STATUT_RESIDENCE, 
                          PRODUIT_NOM,
                          PRODUIT_CAT_NUM,
                          PRODUIT_CAT,
                          PRODUIT_TYPE_NUM,
                          PRODUIT_TYPE,
                          PRODUIT_SOUS_TYPE_NUM,
                          PRODUIT_SOUS_TYPE,
                          PLACES,
                          PLACES_ASH,
                          VALIDITE_DEBUT,
                          TARIF,
                          DATE_MAJ_DISPOS
                              ]
                          ;

                           

                          You are created this as your Inline Data, which is also syntacticaly wrong.

                          so please refer QVW file which i provided you as earlier.

                           

                            • Re: Help : Two tables to one table
                              Ankri Laurent

                              Hi

                              Sorry, but I don't understand

                              You also don't have instruction from in the application that you have sent to me.

                               

                              Thanks

                                • Re: Help : Two tables to one table
                                  vishal waghole

                                  Hi,

                                   

                                  As per your requirement i created dummy data with this two table

                                  named as QV_RET_DEPENDANCE_VW and QV_RET_CHAMBRES_DISPO.

                                   

                                  QV_RET_DEPENDANCE_VW:

                                  LOAD * Inline [
                                  RESIDENCE_NUM,     GIR_1_2,     GIR_3_4,     GIR_5_6            /* This line is the you tables column name */
                                  001,               111,          222,          333,                                                         /*from this line is data for table with respect to column name lable on header */
                                  002,               444,          555,          666]
                                  ;

                                  Join

                                  QV_RET_CHAMBRES_DISPO:
                                  LOAD * Inline [
                                  RESIDENCE_NUM,      PRODUIT_NOM,      TARIF                 /* This line is the you tables column name */
                                  001,               100,               1000,                                                                /* from this line is data for table with respect to column name lable on header */
                                  001,               200,               2000,
                                  001,               300,               3000,
                                  002,               200,               9000,
                                  002,               400,               2500
                                  ]
                                  ;

                                  FINAL_QV_RET_DEPENDANCE_VW:
                                  LOAD RESIDENCE_NUM,
                                  PRODUIT_NOM,
                                  TARIF,
                                  GIR_5_6,
                                  (
                                  TARIF + GIR_5_6)*30.5 as TARIF_GIR

                                  Resident QV_RET_DEPENDANCE_VW;

                                  DROP Table QV_RET_DEPENDANCE_VW;     

                                   

                                  Stil if you have doubt then elaborate me.

                                   

                                  Thanks