help scripting to handle values in field

[brunobertels]

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

[eduardo_sommer]

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

[sunny_talwar]

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;

[settu_periasamy]

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;

[sunny_talwar]

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;

[brunobertels]

Hi

Thanks for the reply

Need to test this and adapt this script to ma data load.

It may helps me

Regards

Bruno

[brunobertels]

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