Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

brunobertels
Valued Contributor

help scripting to handle values in field

Hi Qlickers

I have an excel file like below (and attached)

  

CommercialTaux de transfo
Zone1
Nom118,75
Nom221,74
Nom318,18
Nom47,69
Nom56,9
Nom63,03
Nom76,67
Nom815,62
Nom93,57
Nom109,37
Total9,15
Zone2
Nom114,35
Nom127,89
Nom1312,5
Nom144,76
Nom1511,76
Nom162,5
Nom1712,5
Nom187,14
Nom193,7
Nom208,82
Nom2116,67
Nom2219,44
Nom2322,73
Nom248
Nom254,76
Nom263,03
Nom2712,9
Total8,16
Total France8,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 :

 

CommercialZoneTaux de transfo
Zone1
Nom1Zone118,75
Nom2Zone121,74
Nom3Zone118,18
Nom4Zone17,69
Nom5Zone16,9
Nom6Zone13,03
Nom7Zone16,67
Nom8Zone115,62
Nom9Zone13,57
Nom10Zone19,37
Total9,15
Zone2
Nom11Zone24,35
Nom12Zone27,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 :

   

CommercialZoneTotal for ZoneTaux de transfo
Zone1
Nom1Zone19,1518,75
Nom2Zone19,1521,74
Nom3Zone19,1518,18
Nom4Zone19,157,69
Nom5Zone19,156,9
Nom6Zone19,153,03
Nom7Zone19,156,67
Nom8Zone19,1515,62
Nom9Zone19,153,57
Nom10Zone19,159,37
Total9,15

And drop the line Zone 1 and Total from the column Commercial

Help will be very appreciate

Regards

Bruno

6 Replies
eduardo_sommer
Valued Contributor

Re: help scripting to handle values in field

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

MVP
MVP

Re: help scripting to handle values in field

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;


Capture.PNG

Re: help scripting to handle values in field

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;

MVP
MVP

Re: help scripting to handle values in field

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
Valued Contributor

Re: help scripting to handle values in field

Hi

Thanks for the reply

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

It may helps me

Regards

Bruno

brunobertels
Valued Contributor

Re: help scripting to handle values in field

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