8 Replies Latest reply: Dec 9, 2015 6:08 AM by Diane Nicholls RSS

    Mapping?!?!

    Diane Nicholls

      Hi All,

       

      I have two tables, Invoices and Disputes. I could joint these tables using 'Invoice Number', but I don't really need the disputes table, I just need to see in and invoice sits in the dispute table. In English, so to speak, this is the formula:

       

      if Invoice.Invoice_Number = Dispute.Invoice_Number then 'Disputed' else 'Not 'Disputed'

       

      How can I do this is QlikView?

       

      I think I need to use mapping, but I just can't get my head round it!

       

      Thanks for your help

       

      Di

       

       

        • Re: Mapping?!?!
          Stefan Wühl

          Maybe like this:

           

          MAP:

          MAPPING LOAD

          Invoice_Number, 'Disputed' as F2

          FROM Dispute;

           

          Invoices:

          LOAD

               Invoice_Number,

               ApplyMap('MAP', Invoice_Number, 'Not Disputed') as DisputeFlag,

               ...

          FROM Invoice;

           

          See also:

          Don't join - use Applymap instead

            • Re: Mapping?!?!
              Diane Nicholls

              Hi,

               

              So ideally I need to load my disputes first, then my invoices table?

               

              Where does 'F2' come into it?  Is that what you have just called it?

               

              Sorry, I obviously want the answer, but I also want to understand why its done that way so I can apply it to other apps when needed!

                • Re: Mapping?!?!
                  Diane Nicholls

                  Also, I have 4 loads of my invoice table, different data being added at different times, where should I place the load for the mapping? At the start?

                   

                  Sorry if I'm being a pain!

                   

                  Di

                  • Re: Mapping?!?!
                    Stefan Wühl

                    The table for the mapping load 'MAP' needs to be loaded be for it can be used in ApplyMap() function.

                     

                    It's a temporary table (not existing in the data model after the script executed), and must hold exactely two fields.

                    The name of the fields does not matter, so I just called the second F2 (field 2). The first field is used for the mapping lookup, the second field is used to retrieve the result of the mapping.

                      • Re: Mapping?!?!
                        Diane Nicholls

                        Hi,

                         

                        By golly I think I've got it, but................................Why does it come through as an expression, and not a dimension, which I really need it to be?

                         

                        Is there any way it could become an dimension?

                         

                        Thanks, by the way, your a star!

                         

                        Di

                          • Re: Mapping?!?!
                            Stefan Wühl

                            Not sure what you mean with 'it comes through as an expression'?

                             

                            The load script should just create a new field 'DisputeFlag' with two possible values 'Disputed' and 'Not Disputed'.

                             

                            You should be able to use this field as a dimension without any problems (or within an expression when using an appropriate aggregation, like (Count(DisputeFlag) ).

                            There should be no difference to all your other fields in your data model with regard to how you can use the field.

                             

                            Which software are you using Qlik Sense or QlikView?

                        • Re: Mapping?!?!
                          kushal chawda

                          Can you post the sample QVW file with script

                      • Re: Mapping?!?!
                        Diane Nicholls

                        Hi All,

                         

                        Thank you for helping me out.

                        I actually went with 'Exisits' in the end, but have learnt a lot from your replies, and can now use Mapping!

                         

                        Thank you again

                         

                        Di