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

How to convert my load script to read excel file ?

paulyeo11 — Wed, 10 Jan 2018 08:45:45 GMT

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.

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 08:54:31 GMT

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

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

paulyeo11 — Wed, 10 Jan 2018 08:57:54 GMT

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 ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 09:04:28 GMT

So?

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

paulyeo11 — Wed, 10 Jan 2018 09:07:21 GMT

I get stuck.

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 09:09:22 GMT

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

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

paulyeo11 — Wed, 10 Jan 2018 09:14:22 GMT

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],

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 ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 09:22:20 GMT

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);

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

paulyeo11 — Wed, 10 Jan 2018 09:27:20 GMT

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 ?

Anil_Babu_Samineni — Wed, 10 Jan 2018 09:53:28 GMT

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

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

paulyeo11 — Wed, 10 Jan 2018 09:55:45 GMT

Hi Anil

Thank you very much.i will do it manually.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

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

Anil_Babu_Samineni — Wed, 10 Jan 2018 11:26:38 GMT

If applicable please close this thread

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

paulyeo11 — Wed, 10 Jan 2018 12:07:39 GMT

Hi Anil

Thank you for your sharing. I have already closed.