9 Replies Latest reply: Aug 29, 2016 9:49 AM by Sarah Rosen RSS

    Pivot on Data Load

    Sarah Rosen

      I was looking at Pivot Up while loading data | Qlik Community

      where somebody asks :

       

      Customer IDCategory TypeCategory Value
      1AA1
      2AA2
      3AA3
      4AA4
      1BB1
      2BB1
      3BB2
      4BB3
      1CC1
      2CC2
      3CC3
      4CC4
      5CC1

       

      and while loading the data in QV I want to load it in the following format:

       

      Customer IDABC
      1A1B1C1
      2A2B1C2
      3A3B2C3
      4A4B3C4
      5--

      C1

       

       

      I have this same question, but the answer assumes there are only a couple of category types. Imagine I have A,B,C all the way up to something like AZZB ..or just a lot of different categories, or a new category gets added every so often and i'd like to bring it in anyway....  If I have  unknown category types, or if a new category type comes into my Data source, or I have thousands of different category types,  is there a way to make this dynamic where I do not hard code "if a then value of a as a " type statement. Where it's like...for each Category Type , Category Value as Category Type Name ?    

        • Re: Pivot on Data Load
          Marcus Sommer

          Are you sure that you really need these kind of table-structure within your datamodel? If yes take a look here: The Generic Load.

           

          - Marcus

            • Re: Pivot on Data Load
              Sarah Rosen

              Thank you.  I have tried a generic load before but it was taking hours because of the volume of the data, and to have to then join it again was inefficient.  I was hoping there was a way to add columns as the loader goes through the rows with a for each statement or something like that.

                • Re: Pivot on Data Load
                  Marcus Sommer

                  A transformation with the generic load (and the opposite the crosstable load) needs generally a lot of performance and there are not really alternatives which perform significantly better (you could do it with manually scripting - joins and some other stuff within some loops). But you don't need to perform this again and again with all your data if you implement an incremental load-approach, here is some stuff for this topic (and some more): Advanced topics for creating a qlik datamodel.

                   

                  Nevertheless I come to my question from above - do you really need this within the script? What do you do with these table? Which further transformations or calculations are needed? Maybe it's better to ask for the aim instead of an intermediate step.

                   

                  - Marcus

                    • Re: Pivot on Data Load
                      Sarah Rosen

                      This is my first app. So I'd love input from an expert.  I'm not sure of all the reasons I need to do this. One of the reasons is I have a dynamic pivot table on the front end and I also have some calculations that need to know the other Category Types for that Customer ID.   Like, I have one calculation that wants, for say, customer ID 1, wants to sum the Category Value for C, but only those Customer IDS for that month that also have a B value greater than 0.  I also need to know for each month the Highest B by the account number, so on the back end I do some groupings to get to this. but I'm not sure how to do it without pivoting?  I also on the front end have a dynamic pivot: I'll choose the Category name from an arbitrary island list of category types, and then  I have a Chart with a bunch of variables, vCategory1, vCategory2 and so on.  The conditional show is  =GetSelectedCount(Category) > 0  ,=GetSelectedCount([Category]) > 1  ..and so on for each expression, and then the definition is =Sum( $(vCategory1))  ...and so on.  My variables are...for example, vCategory1  is =SubField(vCategory,',',1)   and vCategory is =GetFieldSelections(Category,', ', 25) .    If it wasn't pivoted on the back end, then if I choose Category A and B, then for each customer, for example the chart would have

                       

                      CustomerID  Type Quantity

                      1                    A 30

                      1                   B 30

                       

                       

                      or, I would want them on the same line but when I do it in that way and do sum of A and Sum of B- then it does this:

                      Customer ID   A     B

                      1                    30     0

                      1                    0          30

                       

                      But ideally, as with my dynamic pivot, it has

                      Customer ID  A        B

                      1                    30     30

                      2     ..     ...

                       

                      and that's what I would like..    And as far as this is concerned, I still want to be able to know , for that month, if that customer had more than 0 in the "A"s and I'm not sure how to do that without grouping it somehow. 

                        • Re: Pivot on Data Load
                          Marcus Sommer

                          For a first app it sounds quite complicated and maybe more as necessary. It's further not quite clear for me what do you want to achieve. Your mentioned ideal pivot is the qlik pivot default behaviour. For additionally conditions which aren't directly selectable could you apply calculated dimensions within the pivot or as listbox expression or with set analysis conditions within the expressions (in the case it must be fully dynamically - otherwise there are also some precalculations within the script thinkable - some aggregations as separate tables or as flags or flags created per Peek() or Previous() ?). Those calculations could be need a lot of performance whereby if you apply a huge crosstable and quite probably a lot of if-loops it won't be rather better.

                           

                          Here you will find very useful informations about Calculated Dimensions and A Primer on Set Analysis and more practically Set Analysis: syntaxes, examples.

                           

                          - Marcus

                            • Re: Pivot on Data Load
                              Sarah Rosen

                              Thanks Marcus, i'll look at those. and ha ha, it's not the default behavior for me!  This has been something where I work on it, and if it doesn't work, I change things until it does work. So there could have been a different reason I did one of those things in a certain way..that caused something else to do something else in a certain way..and so on.  I've been looking for calculated dimensions they never work for me how I want them too.  Thanks I'll look that over! 

                    • Re: Pivot on Data Load
                      Anil Babu

                      Is This you are expecting?

                      • Re: Pivot on Data Load
                        Or Shoham

                        Something that's worth a try:

                        1) Dump a sample of your data into a CSV/Excel file.

                        2) Using the File Load wizard, select the Crosstable option (should be on the third screen).

                        3) Finish the process, load your data, and make sure you're getting the expected result.

                        4) Adapt the generated code for your original data source.

                         

                        I've done this before, and it works well - but depending on your scaling, it may not be relevant for your use case.