Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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],
Hi,
Please explain ur issue with some examples so i can help u.
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.
HI
Please refer the applicatio attached and script in it.
Awesome.
That really helped pushing me into the right direction 🙂
Thank you very much for your help, it is much appreciated.
Hi,
If its correct please veryfy the answer. so other can use this answer.
Hi
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
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],
Thank you so much for your help. It works!
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...