Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot on Data Load

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 ?    

9 Replies
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

Anil_Babu_Samineni

Is This you are expecting?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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.

Not applicable
Author

Yes, but I'm trying to do it in the load because I have other functions that depend on the ID and how many A's are in a specific ID at a certain time compared to other Ids

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

Not applicable
Author

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. 

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

Not applicable
Author

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! 

Or
MVP
MVP

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.