Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to convert my load script to read excel file ?

Hi All

I have a load script which run for 6 years. Now the raw data file need to change to excel file.

May i know what is the fast way to modify my script ?

I mean now my load script read txt file , but now raw data file change to excel file xls file format.

One option is :-

I can convert excel file to txt file format , but when i try to convert , it does not able to get 100%. so this option not possible.

Second option is :-

I need to modify my script. which need to spend min 4 hour.

Does any one here , can suggest me other approach which is more productive.

1 Solution

Accepted Solutions

Re: How to convert my load script to read excel file ?

May be

Ctrl + F and use Keyword (ansi, fix, no labels, header is 0, record is line); with Replace of (ooxml, embedded labels, table is Sheet1);


12 Replies

Re: How to convert my load script to read excel file ?

options i have

1) May be use Ctrl + F to replace the string from and to for all Tabs?

2) If you stored the path in Variable just replace that

paulyeo11
Valued Contributor II

Re: How to convert my load script to read excel file ?

Hi Anil

I just try Control + F in excel is find and replace.

PAul

Re: How to convert my load script to read excel file ?

So?

paulyeo11
Valued Contributor II

Re: How to convert my load script to read excel file ?

I get stuck.

Re: How to convert my load script to read excel file ?

Can you come up with what you are doing here. May be sample script and you want to accomplish ??

paulyeo11
Valued Contributor II

Re: How to convert my load script to read excel file ?

Hi Anil

Below is my script , it read my raw data file txt format no issue. But then now the data file in XLS format :-

//LOAD left(FileBaseName(), 4) AS Report4_invc,


load


//'TDSS' as SOURCE_STK,


'TDSS' as SOURCE,



Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365)))*12)) as  [No of Months_],


//Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@180:190,'DD/MM/YYYY'))/365*2)))*12)) as  [No of Months_],


Ceil(Num(if(num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365*2))=0,1,num(((date(today(),'DD/MM/YYYY') - date(@168:179,'DD/MM/YYYY'))/365*2)))*12)) as  [No of Months],


@168:179T as [L_issue],

@180:190T as [L_move],


year(@180:190T) as [YEAR_],


Date(Monthstart(@180:190T), 'YY MMM') as YearMonth,


// Right(year(TempDate),2) as YEAR_,


Right(year(@180:190T ),2) as YEAR,

year(@180:190T)                                          as Year,




if(date(@180:190,'DD/MM/YYYY') > '$(YearBoundary3)',dual('No', 0), dual('Yes', 1)) as  [Purchase > 3yr No Sales],


if(date(@168:179,'DD/MM/YYYY') > '$(YearBoundary3)',dual('No', 0), dual('Yes', 1)) as  [No Sales > 3yr],


if(date(@180:190,'DD/MM/YYYY') > '$(YearBoundary2)',dual('No', 0), dual('Yes', 1)) as  [Purchase > 2yr No Sales],


if(date(@168:179,'DD/MM/YYYY') > '$(YearBoundary2)',dual('No', 0), dual('Yes', 1)) as  [No Sales > 2yr],



if(date(@180:190,'DD/MM/YYYY') > date(@168:179,'DD/MM/YYYY'), dual('No', 0), dual('Yes', 1)) as  [Pur date more recent then last invoice date],


if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','0','1') as  [FLAG_2YR],


if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','0','1') as  [FLAG_3YR],


//


if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','0','1')

& ' / ' &

if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','0','1') as  [FLAG_2YR_3YR],


if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)','1')

& ' / ' &

if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)','1') as  [FLAG_2YR_3YR_],


//   


if(date(@168:179,'DD/MM/YYYY')>'$(YearBoundary3)' or


  date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary3)' or


  date(@168:179,'DD/MM/YYYY')>'$(YearBoundary2)' or


  date(@180:190,'DD/MM/YYYY')>  '$(YearBoundary2)',


  '0',


  if(date(@168:179,'DD/MM/YYYY')<='$(YearBoundary3)' or


    date(@180:190,'DD/MM/YYYY')<='$(YearBoundary3)' or


    date(@168:179,'DD/MM/YYYY')<='$(YearBoundary2)' or


      date(@180:190,'DD/MM/YYYY')<='$(YearBoundary2)', '1', null()))

       

    as [FLAG_2YR_3YR_F],


@1:11T as [PRODUCT_CODE_INV],


// @1:11T as [product code],


// @12:42T as [desc],


@12:42T as [DESC_INVENTORY_TABLE],



if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

,if (subfield(@12:42T,' ',2)='TS1070', 'TS'

,if (subfield(@12:42T,' ',4)='5M', '5M'

,if (subfield(@12:42T,' ',2)='TS1070', 'TS1070'

,if (subfield(@12:42T,' ',3)='CABLE', 'CABLE'

,if('Brand'='','','OTHER')))))) as [TS],



ApplyMap('BrandMap', subfield(@12:42T,' ',2), 'No') AS Product_Class_,



// subfield(@12:42T,' ',1) as Brand_inv,


if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

,if (subfield(@12:42T,' ',1)='BECKHOFF','BECKHOFF'

,if (subfield(@12:42T,' ',1)='OMRON', 'OMRON'

,if (subfield(@12:42T,' ',1)='GE', 'GE FANUC'

,if (subfield(@12:42T,' ',1)='MITSUBISHI', 'MITSUBISHI'

,if (subfield(@12:42T,' ',1)='HENGSTLER', 'HENGSTLER'

,if (subfield(@12:42T,' ',1)='YAMATAKE', 'YAMATAKE'

,if (subfield(@12:42T,' ',1)='PHOENIX', 'PHOENIX CONTACT'

,if (subfield(@12:42T,' ',1)='P&F', 'PEPPERL+FUCHUS'

,if (subfield(@12:42T,' ',1)='YASKAWA', 'YASKAWA'

,if (subfield(@12:42T,' ',1)='HONEYWELL', 'HONEYWELL'

,if (subfield(@12:42T,' ',1)='PILZ', 'PILZ'

,if (subfield(@12:42T,' ',1)='SCHAFFNER', 'SCHAFFNER'

,if (subfield(@12:42T,' ',1)='REDLION', 'REDLION'

,if (subfield(@12:42T,' ',1)='WEST', 'WEST'

,if (subfield(@12:42T,' ',1)='VISOLUX', 'VISOLUX'

,if (subfield(@12:42T,' ',1)='TDK', 'TDK'

,if (subfield(@12:42T,' ',1)='TNS', 'TNS'

,if (subfield(@12:42T,' ',1)='MOXA', 'MOXA'

,if (subfield(@12:42T,' ',1)='AUTENTO', 'AUTENTO'

,if (subfield(@12:42T,' ',1)='HMS', 'HMS'

,if('Brand'='','','OTHER')))))))))))))))))))))) as [BRAND_INV_SUB],


if (subfield(@12:42T,' ',1)='HAKKO','HAKKO'

,if (subfield(@12:42T,' ',1)='BECKHOFF','BECKHOFF'

,if (subfield(@12:42T,' ',1)='OMRON', 'OMRON'

,if (subfield(@12:42T,' ',1)='GE', 'GE FANUC'

,if (subfield(@12:42T,' ',1)='MITSUBISHI', 'MITSUBISHI'

,if('Brand'='','','OTHER')))))) as [BRAND_INV],


// ApplyMap('BrandMap', subfield(@12:42T,' ',1), 'OTHERS') AS BRAND_INV,

//    ApplyMap('BrandInvMap', subfield(@12:42T,' ',1), 'OTHERS') AS BRAND_INV_SUB,


@43:53T as [QTY_AVA1],


if(right(@43:53T,1)='-', '-' & left(@43:53T, len(@43:53T)-1),@43:53T) as QTY_AVA,




if(right(@54:64T,1)='-', '-' & left(@54:64T, len(@54:64T)-1),@54:64T) as QTY_O,


(@43:53T)*(@65:75T) as D_AVA_AMT,


@65:75T as [COS],

@54:64T as [QTY_O1],

(@54:64T)*(@65:75T) as C_ONHAND_AMT,

@76:81T as [S_1],

if(right(@76:81T,1)='-', '-' & left(@76:81T, len(@76:81T)-1),@76:81T) as S1,


@82:87T as [S_2],

if(right(@82:87T,1)='-', '-' & left(@82:87T, len(@82:87T)-1),@82:87T) as S2,


@88:93T as [S_3],

if(right(@88:93T,1)='-', '-' & left(@88:93T, len(@88:93T)-1),@88:93T) as S3,


@94:99T as [S_4],

if(right(@94:99T,1)='-', '-' & left(@94:99T, len(@94:99T)-1),@94:99T) as S4,


@100:105T as [S_5],

if(right(@100:105T,1)='-', '-' & left(@100:105T, len(@100:105T)-1),@100:105T) as S5,


@106:111T as [S_6],

if(right(@106:111T,1)='-', '-' & left(@106:111T, len(@106:111T)-1),@106:111T) as S6,


@112:117T as [S_7],

if(right(@112:117T,1)='-', '-' & left(@112:117T, len(@112:117T)-1),@112:117T) as S7,


@118:123T as [S_8],

if(right(@118:123T,1)='-', '-' & left(@118:123T, len(@118:123T)-1),@118:123T) as S8,


@124:129T as [S_9],

if(right(@124:129T,1)='-', '-' & left(@124:129T, len(@124:129T)-1),@124:129T) as S9,


@130:135T as [S_10],

if(right(@130:135T,1)='-', '-' & left(@130:135T, len(@130:135T)-1),@130:135T) as S10,


//Averaging

(((((@76:81T)+(@82:87T)+(@88:93T)+(@94:99T)+(@100:105T)+(@106:111T)+(@112:117T)+(@118:123T)+(@124:129T)+(@130:135T)+(@136:141T)+(@142:147T))/12)*(@65:75T))) as [B_COGS],


num(((@76:81T)+(@82:87T)+(@88:93T)+(@94:99T)+(@100:105T)+(@106:111T)+(@112:117T)+(@118:123T)+(@124:129T)+(@130:135T)+(@136:141T)+(@142:147T))/12) as [AVERAGE],


@136:141T as [S_11],


if(right(@136:141T,1)='-', '-' & left(@136:141T, len(@136:141T)-1),@136:141T) as S11,


// @142:147T as [S_12],



// @142:147T as [S_12],


@142:147T as [S_12],


if(right(@142:147T,1)='-', '-' & left(@142:147T, len(@142:147T)-1),@142:147T) as S12,



// @148:157T as [PROD_CLS],


@191:200T as [onorder],


@235:285T as [desc_short],


@286:296T as [invc_date],


Date(Monthstart(@286:296T), 'DD-MMM-YYYY') as YearMonthDay1,

@297:319T as [manuf_no],

@297:319T as [PART_NO_],

@297:319T as [PART_NO],

@320:354T as [M_DATE_],


@331:358T as [PRO_CLS_],


Date(Monthstart(@320:354T), 'DD-MMM-YYYY') as M_DATE

// Date(Monthstart(@344:354T), 'DD-MMM-YYYY') as YearMonthDay1



FROM $(vRAWPath)$(vFile3) (ansi, fix, no labels, header is 0, record is line);


//LOAD * INLINE [

//    F1, LTMonths

//    A, 0.5

//    B, 1

//    C, 1.5

//    D, 2

//];

Re: How to convert my load script to read excel file ?

May be

Ctrl + F and use Keyword (ansi, fix, no labels, header is 0, record is line); with Replace of (ooxml, embedded labels, table is Sheet1);


paulyeo11
Valued Contributor II

Re: How to convert my load script to read excel file ?

Hi Anil

I still not sure what you mean control + F ?

So i need to replace below line :-

@168:179T as [L_issue],

excel header name right ?

Paul Yeo

Re: How to convert my load script to read excel file ?

There is no best practice to that, Instead you could use Manually

Community Browser