topic Pastel Data Table in QlikView

Pastel Data Table

Fri, 25 Feb 2011 09:17:52 GMT

We have been importing our data from a financial package (Pastel) for a while now, but it does involve some manual work before we can get it into Qlikview.

I am hoping I can add some formula's to eliminate this need in order to setup Qlikview directly to my pastel data.

I have created a very small extract of the data in order to explain my problem, and also attached a sample Qlikview file in case my explination is not clear.

The data from Pastel comes in as a Rollup for a certain account. For example:

1000/1010/303 (Sales/Parts & Material/Installation) would indicate that the amount for total sales is split between "Spares" (45%) and "Installation" (55%)

I have created Category's on how I would like to see these figures. Thus in the category "Spares" I would like to only refelct 45% of the amount for "1000/1010/303 (Sales/Parts & Material/Installation)" and in the category "Installations" I would like to add 55% of the amount for "1000/1010/303 (Sales/Parts & Material/Installation)"

As you can see I have multiple of such accounts that need to be split into categories. We obviously have the % split for each account but for the purpose of the sample I only focused on one item.

The idea is to have a bar chart only of the 5 main categories in the end.

Thank you in advance for your help.

Pastel Data Table

maneshkhottcpl — Fri, 25 Feb 2011 10:07:24 GMT

Hi,

Please explain ur issue with some examples so i can help u.

Pastel Data Table

Fri, 25 Feb 2011 10:16:34 GMT

If you look at the Qlikview file I attached you will notice that I have 3 Categories:

Installations, Service, Spares

But the data that comes from the accounting package is rolled up or merged.

So i have an account called "1000/1010/303 (Sales/Parts & Material/Installation)" with a value of R18 130.27. But this amount needs to be split into 45% allocated to the "Spares" category and 55% to the "Installation" category.

Thus from this line I need to have the following values in the table from top to bottom (if you consider just this one item):

Spares: R8 158.62 (R18 130.27 x 45%)

Installation:R9 971.65 (R18 130.27 x 55%)

I want to breakdown all the accounts in the same fashion in order to have a table only with the values of the Main categories, and then draw a bar chart with those values.

Hope this makes more sense.

Pastel Data Table

maneshkhottcpl — Fri, 25 Feb 2011 10:27:27 GMT

Please refer the applicatio attached and script in it.

Pastel Data Table

Fri, 25 Feb 2011 10:46:49 GMT

Awesome.

That really helped pushing me into the right direction 🙂

Thank you very much for your help, it is much appreciated.

Pastel Data Table

maneshkhottcpl — Fri, 25 Feb 2011 11:10:19 GMT

Hi,

If its correct please veryfy the answer. so other can use this answer.

Pastel Data Table

Fri, 25 Feb 2011 11:24:12 GMT

I have just finished my testing but seem to have encountered a new problem.

LOAD Account,
if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.55) as Labour,
if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.45) as [Breakdown / Services],
if (Account='1000/1015/203 (Sales/Transport/Boilers)',Actual*.50) as Installation,
if (Account='1000/1015/203 (Sales/Transport/Boilers)',Actual*.50) as [Breakdown / Services],
if (Account='1000/1010/303 (Sales/Parts & Material/Installation)',Actual*.45) as Spares,
if (Account='1000/1010/303 (Sales/Parts & Material/Installation)',Actual*.55) as Installation,
if (Account='1000/1005/305 (Sales/Labour/Service Contract)',Actual*.65) as Labour,
if (Account='1000/1005/305 (Sales/Labour/Service Contract)',Actual*.35) as [Service Contracts]

As you know I want to break each account into a Category. So when I add all the categories it obviously creates duplicates as multiple accounts could fall under the same category.

Qlikview then gives me a error when I import stating that the fields must be unique.

Regards

Pastel Data Table

maneshkhottcpl — Fri, 25 Feb 2011 11:37:26 GMT

hi,

u r using same Alise name twice,

if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.45) as [Breakdown / Services],

if (Account='1000/1015/203 (Sales/Transport/Boilers)',Actual*.50) as [Breakdown / Services],

try the follows

if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.45,if (Account='1000/1015/203 (Sales/Transport/Boilers)',Actual*.50)) as [Breakdown / Services],

Pastel Data Table

Fri, 25 Feb 2011 11:44:20 GMT

Thank you so much for your help. It works!

Pastel Data Table

Fri, 25 Feb 2011 12:03:19 GMT

hi...

if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.55) as Labour

if (Account='1000/1005/305 (Sales/Labour/Service Contract)',Actual*.65) as Labour

You are using same alias name "Labour" .

try this

if (Account='1000/1005/203 (Sales/Labour/Boilers)',Actual*.55),if (Account='1000/1005/305 (Sales/Labour/Service Contract)',Actual*.65)as labour

hope this helps for you...