Manually modifying dimention *values* in pivot table

[sibrulotte]

Hi,

so my raw data is scarse in fields available. For exemple, I only have one description field for an accounting transaction, and what is written there in the raw data, is not necessarely what I would like to have appear in the pivot table, but I cannot modify the raw data (or at least, I cannot make a modification and have it appear rapidly since my source is a data warehouse updated with production data once a day).

So let's say my dimention [Description] has "Cookies and milk" in the raw data for a [AK_aux] (which is my transaction primary key), but I would like to have "Oreos and juice" written instead in the pivot table. And that has to be redefinable easily, since it is not standard (so I can't script a find and replace, it'll be a judgment call from the analyst).

Any ideas?

[Gysbert_Wassenaar]

You could try to use an inputfield. See attached example. Note, input field values are user specific and cannot be shared between users. So, that solution may not work for you if your document is deployed to Qlikview Server. 

[sibrulotte]

Sounds like a plan. I have a licence that only lets me open 5 qvw that were not created by myself. I'm not too sure I'll use it on the file you sent, since I'm pretty sure I could make sens of it if you explain how to tie the pivot table and the input field together.

please ?

[Gysbert_Wassenaar]

Don't use a recovery because that will change the license of your Personal Edition with the result you can open the files you created before anymore.

I'll try to explain. I have a Sales table with a Product field. I'd like to substitute the product names on the fly with descriptions of my own. First I load the Sales data:

Sales:

LOAD [Transaction Date],

     Region,

     SalesPerson,

     Product,

     Sales    

FROM

[salestodate.xls]

(biff, embedded labels, table is Sheet1$);

Next I define an inputfield:

INPUTFIELD ProductDescription;

And then I load some bogus descriptions for each product. You could use another excel table for source values. This is just an example so an inline table will do here.

Products:

Load * inline [

Product, ProductDescription

Apple, a

Banana, b

Cherry, c

Kiwi, k

Lemon, l

Mango, m

Orange, o

Pineapple, p

Strawberry, s

];

In the UI I add a table box with the Product and ProductDescription fields. In this table box I can enter my own product descriptions by hovering the mouse on the right side of a cell and then clicking the triangle icon.

In the pivot table I use ProductDescription instead of Product so the Pivot table shows the descriptions I entered in the table box.

[sibrulotte]

Right,

I'll try that this after noon.

Thank you,

will let you know if I succeeded.

[sibrulotte]

Hi,

so I'm struggling with something:

So I loaded my script with an inputfield [Description complete],

Is there a specific nomenclature (put an coma, put the inputfield before the table where the field will be found, after?) I should use. I tried to put it at the end of the script, and the table box I created with that description does not show any triangle Icon.

[sibrulotte]

So I retried exactly what you did to get a better understanding, and it worked great.

Now I'm trying to recreate it in my file and when I load I get :

Duplicate keys were found for [Description complete]

here's what my script looks like:

(AK_AUX is my product, [Description complete] is my product description.)

GLdetail:

LOAD Année,

     Période,

     AK_AUX,

     SQ,

     Entité,

     Unité,

     Projet,

     Rubrique,

     Nature,

     Mois,

     [Descr. Entité],

     [Descr. Unité],

     [Descr. Projet],

     [Descr. Rubrique],

     [Descr. Nature],

     [Rubrique-Nature],

     [Descr. Rubr.-Nature],

     [Descr. Charte],

     [Débit-Crédit],

     [Bilan-Résultats],

     [Section États],

     Multiplicateur,

     Poste,

     [Bloc Extra.],

     [Segment 1 Extra.],

     [Segment 2 Extra.],

     [Descrtion Extra.],

     Montant,

     [Solde Facture],

     Quantité,

     Date,

     [Descr. Transaction],

     [Type Écriture],

     [Descr. Écriture],

     [No Document],

     [Code Document],

     LD,

     [No. Post-it],

     [Type Document],

     [Code Référence],

     Nom,

     [Code Catégorie],

     [Descr. Catégorie],

     [Code Vendeur],

     Vendeur,

     [Type Transaction],

     Essbase_Flag,

     [Date Time Stamp],

     [RF DVFFCOMI ENGAGEMENTS],

     [# REPORT INVO],

     [DATE REPORT INVO],

     [# REPORT FGJENT],

     [DATE REPORT FGJENT],

     [DATE REPORT INVO1],

     [# REPORT FGJENT1],

     [DATE REPORT FGJENT1]

FROM

(ooxml, embedded labels, table is Feuil1);

Inputfield [Description complete];

Description:

LOAD AK_AUX,

     [Description complete]

FROM

(ooxml, embedded labels, table is Feuil1);

Grand_Livre_virtuo:

LOAD 

     B as [Poste budgétaire],

     H as [Année],

     I as [Période],

     J as [Type transaction],

     K as [Identification],

     L as [Date],

     M as [Description imputation],

     AC as [No Document]

FROM

(ooxml, no labels, header is 3 lines, table is Feuil2);

Calendrier:

LOAD Période,

     Mois

FROM

(ooxml, embedded labels, table is Feuil1);

Budget:

LOAD Année,

     Période,

     Mois,

     Poste,

     Montant as [Montant Budget]

FROM

(ooxml, embedded labels, table is Budget);

[Gysbert_Wassenaar · Accepted solution]

You must make sure AK_AUX only has unique values in the Description table. Check that your Descriptions.xlsx excel file contains only unique product codes. You can comment out the inputfield line to load the data normally to see what gets loaded.

[sibrulotte]

Right, that was the trick. I also had found this: add a RowNo() to get distinct lines, but in deed there were duplicate AK_AUX (with duplicate description), which was not helpfull when inputing new [Description Complete] since double entries were found for the description in my Description: table.

Anyways, that is solved, thank you.