10 Replies Latest reply: Oct 17, 2012 3:34 AM by Dmitry Danilo RSS

    New values in dataset

      Hello.

      I want to make a very simple task in Qlik and almost broke my head. I'm asking for community help.

      I want to show new IDs with comparison to previous month. User will choose a month and will see a table with new IDs in this month.

      For example:

       

      IDDateSoldPrice
      4448                 04.201210,00990,00
      4448                 05.201220,00990,00
      4499                 04.20121,001 890,00
      4544                 04.20122,002 090,00
      4595                 04.20123,002 590,00
      4624                 05.20120,003 190,00
      5096                 05.20120,002 390,00

       

      For 05.2012 user will see next new IDs and additional info about it:

      4624                
      5096                

       

      What is the simpliest way to do it in QlikView?

       

      WBR,
      Dmitry.

        • Re: New values in dataset

          Maybe somebody can point me to the similar discussions or solutions?

           

          WBR,

          Dmitry.

          • Re: New values in dataset
            Gysbert Wassenaar

            For the new ID's the amount sold is 0? If so you can create a table that shows only the ID's where sold = 0. Then if a user selects a date in the listbox then you see only the new ID's for that date. Is that what you need?

            Use a calculated dimension for the ID's: if(Sold = 0, ID) and a listbox for selection the date.

              • Re: New values in dataset

                Unfortunately no. It's just an example, the real amount of data is huge and more complicated.

                It's seems, that such simple calculation is not easy even for a famous Qlikview, it's a great pity...

                  • Re: New values in dataset
                    Gysbert Wassenaar

                    Yes, it's a shame qlikview can't read minds.

                    Could you explain what should happen when? How do we know which ID's are new?

                      • Re: New values in dataset

                        Sorry, maybe I can't explain what I want to get correctly, my English is not perfect. I thought, that my example will clarify what I want to get.

                        Let me try again.

                        I've changed the example (removed 0 from sold):

                         

                        IDDateSoldPrice
                        4448                 04.201210,00990,00
                        4448                 05.201220,00990,00
                        4499                 04.20121,001 890,00
                        4544                 04.20122,002 090,00
                        4595                 04.20123,002 590,00
                        4624                 05.201256,003 190,00
                        5096                 05.20125,002 390,00

                         

                        This data is the small part of real data (for 5 years). Imagine, that this is a full set of data. For this table I want to get the new IDs in 05.2012, comparing to the previous month 04.2012.

                        There is no special attribute in data for new IDs, only ID and date, so I need to compare all IDs in 04.12 with all IDs in 05.12 to find the difference. As you see its 4624 and
                        5096, this IDs appeared only in 05.2012, but I need an expression, which will work on any amount of data.

                         

                        In report it will work in next way:

                        A user will choose any (but only one) month and will see the small table or list with new ID in this month comparing to previous month.

                         

                        May be there is a way to show this new IDs only knowing IDs and dates?

                         

                        WBR,

                        Dmitry.

                          • Re: New values in dataset
                            Gysbert Wassenaar

                            I think I can get the result you want. I added a flag that signals when an ID first appears in the record set. For this to work you do need to make sure the data is ordered by date If necessary you can add that clause to the load statement.

                            Can you check if the attached qvw does what you need?

                              • Re: New values in dataset

                                Thank you, Gysbert.

                                It works perfect on example, but when I add this flag to my existing script - no luck.

                                 

                                First of all, I load data to Qlik from SQL DB using OLEDB and SQL Select script. As I understood, I can't use EXISTS function in SQL Select, only in LOAD statement, am I correct?

                                 

                                So, I've tried to make a LOAD RESIDENT script after SQL select to add a flag with EXISTS function, but it doesen't work, it shows only zeroes.

                                I found, that EXISTS function stop working, when you use it for LOAD RESIDENT script. You can try it, first to load data and then to add a NewFlag to a table:

                                Items:

                                load *

                                inline [...];


                                Newart:

                                load *,

                                if(Exists(ID),0,1) as NewFlag

                                Resident Items;

                                DROP Table Items;

                                 

                                Maybe I'm doing something wrong?

                              • Re: New values in dataset
                                John Witherspoon

                                I'm not sure what you mean by "comparing to previous month", but the attached example shows one possible interpretation and solution - an "AsOf" table.  It uses years instead of months, but is otherwise very similar to your example.

                                 

                                Edit:  Hmmm, no, I'm pretty sure I misinterpreted what you were after.  Sorry.

                                 

                                Edit 2: gwassenar's solution of setting a flag during the load seems good.