15 Replies Latest reply: Feb 24, 2012 5:58 AM by Celambarasan Adhimulam RSS

    change numeric values to text

    chris goudy

      The tables I loaded into qlikview show numeric values for clients and pharmacies.

       

      How do I change the numeric values into the names of the clients and pharmacies?

        • change numeric values to text
          Celambarasan Adhimulam

          Hi,

               What is the relation between numeric values for clients,pharmacies and names of the clients ,pharmacies?

               little more briefer please.

           

          Celambarasan

            • change numeric values to text
              chris goudy

              I'm not exactly sure.

               

              LOAD agentcode,

                   agentname,

                   callduration,

                   callid,

                   id,

                   profileno as Client,

                   ramsno as 'Pharmacy Name',

                   statedescription,

                   timestamp

              FROM

              C:\Users\chrisg\Documents\CallLogs.qvd

              (qvd);

               

               

              This is all the data in the script I have

               

              Does that help anymore, or is there further info I can provide to make it easier to help out?

                • change numeric values to text
                  Celambarasan Adhimulam

                  Hi,

                       Which table has Clientname and Pahrmacy name for its corresponding profileno and ramsno ?

                   

                  Celambarasan

                    • change numeric values to text
                      chris goudy

                      Sorry, I don't know.  Do these tables possibly exist outside of my qlikview model?

                        • change numeric values to text
                          Celambarasan Adhimulam

                          Hi,

                                Check the database what all the tables related to the fields profileno and ramsno.

                                You need them to get the client name and pharmacy name.

                                if you don't the info then ask your client to provide that information.

                           

                          Celambarasan

                            • change numeric values to text
                              chris goudy

                              Ok, I see the table that lists ramsno with the corresponding pharmacy name

                               

                              Here is the syntax of the script that appears when I select it

                               

                              SELECT TOP 1000 [Ramsno]

                                    ,[PharmacyName]

                                FROM [CCP_Compliance].[dbo].[ref_Pharmacies]

                               

                              Do I need to put this directly into the script, or do I need to load this table into the script?

                                • change numeric values to text
                                  Celambarasan Adhimulam

                                  Hi,

                                       If you already made a connection to the database in script means then you can use this script directly.

                                   

                                  Celambarasan

                                    • change numeric values to text
                                      chris goudy

                                      do I use the exact script besides:

                                       

                                      Select Top 1000      ??

                                       

                                      I'm not sure where in the script to put it either

                                        • change numeric values to text
                                          jagan mohan rao appala

                                          Hi,

                                           

                                          Select TOP 1000

                                           

                                          fetches first 1000 rows.  If you just need 1000 rows keep as such, else remove Top 1000.

                                           

                                          Regards,

                                          Jagan.

                                          • change numeric values to text
                                            Celambarasan Adhimulam

                                            Hi,

                                                 Use like this.

                                                

                                            Pharmacy:

                                            SQL SELECT

                                                 [ramsno],

                                                 [PharmacyName]

                                              FROM [CCP_Compliance].[dbo].[ref_Pharmacies];

                                             

                                            CallLog:

                                            LOAD agentcode,

                                                 agentname,

                                                 callduration,

                                                 callid,

                                                 id,

                                                 profileno,

                                                 ramsno,

                                                 statedescription,

                                                 timestamp

                                            FROM

                                            C:\Users\chrisg\Documents\CallLogs.qvd

                                            (qvd);

                                             

                                            Qlikview has feature that it automatically joins the field which has common name.Here you don't need to join the two tables.

                                             

                                            Celambarasan

                                              • change numeric values to text
                                                chris goudy

                                                Works great, thanks a lot for your help!

                                                  • change numeric values to text
                                                    chris goudy

                                                    I'm having trouble with the Client names and data. Here is the script I used, but when I reload it says there is an error

                                                     

                                                    Client: 

                                                    SQL SELECT

                                                          [ProfileNo],

                                                          [PatientName],

                                                          [PatientTelNo],

                                                          [ContactName],

                                                          [ContactTelNo],

                                                          [ContactCellNo],

                                                          [ContactAddress1],

                                                          [ContactAddress2],

                                                          [ContactAddress3],

                                                          [CallIncrementFlag],

                                                          [InitialCallDate],

                                                          [RamsNo],

                                                          [Notes],

                                                      FROM [CCP_Compliance].[dbo].[ex_ProfileMaster];

                                                     

                                                    **************the entire script reads

                                                     

                                                     

                                                     

                                                       

                                                    Pharmacy:

                                                    SQL SELECT

                                                         [ramsno],

                                                         [PharmacyName]

                                                      FROM [CCP_Compliance].[dbo].[ref_Pharmacies];

                                                     

                                                    Client: 

                                                    SQL SELECT

                                                          [ProfileNo],

                                                          [PatientName],

                                                          [PatientTelNo],

                                                          [ContactName],

                                                          [ContactTelNo],

                                                          [ContactCellNo],

                                                          [ContactAddress1],

                                                          [ContactAddress2],

                                                          [ContactAddress3],

                                                          [CallIncrementFlag],

                                                          [InitialCallDate],

                                                          [RamsNo],

                                                          [Notes],

                                                      FROM [CCP_Compliance].[dbo].[ex_ProfileMaster];

                                                     

                                                    LOAD agentcode,

                                                         agentname,

                                                         callduration,

                                                         callid,

                                                         id,

                                                         profileno as Client,

                                                         ramsno as 'Pharmacy Name',

                                                         statedescription,

                                                         timestamp

                                                    FROM

                                                    C:\Users\chrisg\Documents\CallLogs.qvd

                                                    (qvd);

                                            • change numeric values to text
                                              jagan mohan rao appala

                                              Hi,

                                               

                                              Add connection string to database and use the following script below

                                               

                                              TableName:

                                              SQL SELECT TOP 1000

                                                   [ramsno],

                                                   [PharmacyName]

                                                FROM [CCP_Compliance].[dbo].[ref_Pharmacies];

                                               

                                               

                                              Modify your previous script to this

                                              LOAD agentcode,

                                                   agentname,

                                                   callduration,

                                                   callid,

                                                   id,

                                                   profileno as Client,

                                                   ramsno ,

                                                   statedescription,

                                                   timestamp

                                              FROM

                                              C:\Users\chrisg\Documents\CallLogs.qvd

                                              (qvd);

                                               

                                              So that both tables has a relation, ramsno(FieldName) should be common in both the tables.

                                               

                                              Regards,

                                              Jagan.

                                          • change numeric values to text
                                            jagan mohan rao appala

                                            Hi,

                                             

                                            Try this script and check whether any extra columns are fetched, if extra columns are fetched then look for the Client and Pharmacy name columns.

                                             

                                            LOAD

                                                 *

                                            FROM

                                            C:\Users\chrisg\Documents\CallLogs.qvd

                                            (qvd);

                                             

                                            Hope this helps you.

                                             

                                            Regards,

                                            Jagan.