Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlickers
I have an excel file like below (and attached)
Commercial | Taux de transfo |
Zone1 | |
Nom1 | 18,75 |
Nom2 | 21,74 |
Nom3 | 18,18 |
Nom4 | 7,69 |
Nom5 | 6,9 |
Nom6 | 3,03 |
Nom7 | 6,67 |
Nom8 | 15,62 |
Nom9 | 3,57 |
Nom10 | 9,37 |
Total | 9,15 |
Zone2 | |
Nom11 | 4,35 |
Nom12 | 7,89 |
Nom13 | 12,5 |
Nom14 | 4,76 |
Nom15 | 11,76 |
Nom16 | 2,5 |
Nom17 | 12,5 |
Nom18 | 7,14 |
Nom19 | 3,7 |
Nom20 | 8,82 |
Nom21 | 16,67 |
Nom22 | 19,44 |
Nom23 | 22,73 |
Nom24 | 8 |
Nom25 | 4,76 |
Nom26 | 3,03 |
Nom27 | 12,9 |
Total | 8,16 |
Total France | 8,3 |
In the same field I have lines for commercial , named Zone , and a line" total" for each Zone.
First issue :
upper I have a line called Zone1 , Zone2 etc I would like to use this values to create a dimension named Zone to get this :
Commercial | Zone | Taux de transfo |
Zone1 | ||
Nom1 | Zone1 | 18,75 |
Nom2 | Zone1 | 21,74 |
Nom3 | Zone1 | 18,18 |
Nom4 | Zone1 | 7,69 |
Nom5 | Zone1 | 6,9 |
Nom6 | Zone1 | 3,03 |
Nom7 | Zone1 | 6,67 |
Nom8 | Zone1 | 15,62 |
Nom9 | Zone1 | 3,57 |
Nom10 | Zone1 | 9,37 |
Total | 9,15 | |
Zone2 | ||
Nom11 | Zone2 | 4,35 |
Nom12 | Zone2 | 7,89 |
second Issue :
I'am trying to pick 'Total' ( as a value in field "Commercial) to use it as the total for a Zone.
Problem is that there is several lines called Total ( one per Zone).
And I don't know how to pick each one , renamed it as TotalZone1 , TotalZone2 etc
( i can't use a formula to calculate the total per zone because values are % and will give wrong results.
at the end I would like something like that :
Commercial | Zone | Total for Zone | Taux de transfo |
Zone1 | |||
Nom1 | Zone1 | 9,15 | 18,75 |
Nom2 | Zone1 | 9,15 | 21,74 |
Nom3 | Zone1 | 9,15 | 18,18 |
Nom4 | Zone1 | 9,15 | 7,69 |
Nom5 | Zone1 | 9,15 | 6,9 |
Nom6 | Zone1 | 9,15 | 3,03 |
Nom7 | Zone1 | 9,15 | 6,67 |
Nom8 | Zone1 | 9,15 | 15,62 |
Nom9 | Zone1 | 9,15 | 3,57 |
Nom10 | Zone1 | 9,15 | 9,37 |
Total | 9,15 |
And drop the line Zone 1 and Total from the column Commercial
Help will be very appreciate
Regards
Bruno
Hi, you can use the Previous() function, when a Total record is beind handled.
Like
Load Commercial,
if (Commercial = 'Total', Previous(Zone), Zone) as Zone,
etc
In this case, when you are handling a Total record, the load will use the Zone from the previous record.
Eduardo
May be this:
Table:
LOAD Commercial,
[Taux de transfo],
If(Commercial = 'Total', RangeSum(Peek('Key'), 1), RangeSum(Peek('Key'), 0)) as Key
FROM
testDimension.xls
(biff, embedded labels, table is Feuil1$);
Temp1:
LOAD Commercial,
[Taux de transfo]
Resident Table
Where Commercial Like 'Nom*' and Key = 0;
Join (Temp1)
LOAD Commercial as Zone
Resident Table
Where Commercial Like 'Zone*' and Key = 0;
Join(Temp1)
LOAD [Taux de transfo] as [Total for Zone]
Resident Table
Where Commercial Like 'Total' and Key = 1;
Temp2:
LOAD Commercial,
[Taux de transfo]
Resident Table
Where Commercial Like 'Nom*' and Key = 1;
Join (Temp2)
LOAD Commercial as Zone
Resident Table
Where Commercial Like 'Zone*' and Key = 1;
Join(Temp2)
LOAD [Taux de transfo] as [Total for Zone]
Resident Table
Where Commercial Like 'Total' and Key = 2;
FinalTable:
NoConcatenate
LOAD *
Resident Temp1;
Concatenate (FinalTable)
LOAD *
Resident Temp2;
DROP Tables Table, Temp1, Temp2;
Hi,
One more,
T:
LOAD Commercial,
[Taux de transfo]
FROM
[QV - Others\QVcomm\testDimension.xls]
(biff, embedded labels, table is Feuil1$);
NoConcatenate
T1:
Load *,if(len(trim([Taux de transfo]))>0,Peek('Temp_Field'),Commercial) as Temp_Field
Resident T;
DROP Table T;
NoConcatenate
T2:
Load Commercial,
[Taux de transfo],
Temp_Field,
Temp_Field&Commercial as Key
Resident T1 Where len(trim([Taux de transfo]))>0;
Left Join(T2)
Load Temp_Field, [Taux de transfo] as Total Resident T2 Where WildMatch(Commercial,'Total');
DROP Table T1;
Final:
Load Commercial,
Temp_Field as Zone,
[Taux de transfo],
Total as [Total for Zone]
Resident T2 Where not WildMatch(Commercial,'*Total*');
DROP Table T2;
A more dynamic script for more than 2 Zones:
Table:
LOAD Commercial,
[Taux de transfo],
If(Commercial = 'Total', RangeSum(Peek('Key'), 1), RangeSum(Peek('Key'), 0)) as Key
FROM
testDimension.xls
(biff, embedded labels, table is Feuil1$);
Temp:
LOAD Max(Key) as MaxKey
Resident Table;
LET vMaxKey = Peek('MaxKey') - 1;
DROP Table Temp;
FOR i = 0 to $(vMaxKey)
Temp$(i):
LOAD Commercial,
[Taux de transfo]
Resident Table
Where Commercial Like 'Nom*' and Key = $(i);
Join (Temp$(i))
LOAD Commercial as Zone
Resident Table
Where Commercial Like 'Zone*' and Key = $(i);
Join(Temp$(i))
LOAD [Taux de transfo] as [Total for Zone]
Resident Table
Where Commercial Like 'Total' and Key = ($(i) + 1);
NEXT i
LET vConcat = 'NoConcatenate';
LET vTable = 'FinalTable:';
FOR j = 0 to $(vMaxKey)
$(vTable)
$(vConcat)
LOAD *
Resident Temp$(j);
DROP Table Temp$(j);
LET vTable = '';
LET vConcat = 'Concatenate (FinalTable)';
NEXT j
DROP Table Table;
Hi
Thanks for the reply
Need to test this and adapt this script to ma data load.
It may helps me
Regards
Bruno
Hi Sunny
Thank you for your examples.
Need to test the second one as i have more than 2 zone.
I hope i will be able to adapt it to my full script as it is made with ODBC connect to loop thrue multiple excel sheet to get month , week en quarter
Regards
Bruno