topic Re: Pivot on Data Load in QlikView

Pivot on Data Load

Wed, 24 Aug 2016 18:22:54 GMT

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

where somebody asks :

Customer ID	Category Type	Category Value
1	A	A1
2	A	A2
3	A	A3
4	A	A4
1	B	B1
2	B	B1
3	B	B2
4	B	B3
1	C	C1
2	C	C2
3	C	C3
4	C	C4
5	C	C1

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

Customer ID	A	B	C
1	A1	B1	C1
2	A2	B1	C2
3	A3	B2	C3
4	A4	B3	C4
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 — Thu, 25 Aug 2016 07:52:09 GMT

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

Anil_Babu_Samineni — Thu, 25 Aug 2016 08:11:51 GMT

Is This you are expecting?

Re: Pivot on Data Load

Thu, 25 Aug 2016 15:39:04 GMT

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

Thu, 25 Aug 2016 15:41:00 GMT

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

Re: Pivot on Data Load

marcus_sommer — Thu, 25 Aug 2016 17:25:40 GMT

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

Fri, 26 Aug 2016 19:53:35 GMT

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 — Mon, 29 Aug 2016 13:33:24 GMT

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

Mon, 29 Aug 2016 13:49:37 GMT

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

Or — Mon, 29 Aug 2016 14:01:36 GMT

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.