Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

add new column in Qlikview converter

Hi,

I want to add a new field that is '[Can Inv ind]' in my qlikview converter.

While doing this i am getting field not found error.

Kindly help me to resolve this problem.

Pls find below the script

//==========================================================================================

// 1. Convert TXT2QVD

//==========================================================================================

/*******************************************************************************************/

For each TextFile in filelist ('ZSD_0082_*.txt')

DUMP:

load

  *

from $(TextFile) (ansi, txt, delimiter is '|', embedded labels, header is 68 lines, no quotes, filters(

Remove(Col, Pos(Top, 1))

))

//where isnum([Inv. no.]);

where isnum([Reff.Doc No]);

LET x = TextFile;

Let y = mid(x,index(x,'\',-1)+1,len(x)-4-index(x,'\',-1));

store DUMP into _dump_$(y).qvd;

DSR:

load

  rowno() as id_dsr

  ,year(date#(Inv.date,'DD.MM.YYYY')) as Year

  ,num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2)) as month

  ,month(date#(Inv.date,'DD.MM.YYYY')) as Month

  ,weekyear(date#(Inv.date,'DD.MM.YYYY'))&

  if(len(week(date#(Inv.date,'DD.MM.YYYY')))<2,

  0&week(date#(Inv.date,'DD.MM.YYYY')),

  week(date#(Inv.date,'DD.MM.YYYY'))) as Week

  ,left(CMU,2) as Plant

  ,left([SO Name],3) as SU

  ,if(right([Cash Disc.],1) = '-',evaluate(1.00*left([Cash Disc.],len([Cash Disc.])-1)),1.00*[Cash Disc.]) as [Cash Disc.]

  ,if(right([QDInv Rs/%],1) = '-',evaluate(1.00*left([QDInv Rs/%],len([QDInv Rs/%])-1)),1.00*[QDInv Rs/%]) as [QDInv Rs/%]

  ,date#(Inv.date,'DD.MM.YYYY') as Inv.date

  , num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2))&COC as key_allocation

  , num(year(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'))&mid(date(date#(Inv.date,'DD.MM.YYYY'),'DD.MM.YYYY'),4,2))&[Sold-To Code] as key_lakshya

  , [Sold-To Code]&Plnt&[Material Code] as key_pending_orders

// =====================================================================================================================================================================

  ,DCh

  ,Cust.g5

// ,CG2 // GMU better take the first three letters of the area offices, these sales unit are linked to the customer

  ,Cust.g4

  ,Rg

  ,[SO Name]

  //,[Sales Dist.] //Warehouse as linked to the customer

  ,COC as Cnty

  ,County

  ,City

  ,[City Name]

  ,CG1

  ,[Sold-To Code]

  ,[Sold-To Name]

  ,[Ship-To Code]

  ,[Ship-To Name]

// ,Delcreder // Not used

  ,[Sh SGrp] // Sgr= District officer

  ,Cust.g3

  ,[Cust Grp]

  ,Plnt

  ,[Plant Name]

  ,CMU

  ,[CMU Desc.]

  //, right([CMU Desc.],2) as Rail_or_Road

// ,MG2 as prod_grp // not to be used

  ,[Material Code]

  ,[Material Desc]

  //,InTy

  , [Reff.Doc No] as [Inv. no.]

  ,Contract

// ,ShC // always "0"

  ,Inco

  ,MODE as Trnsp

  ,Route

  ,Dist

  ,Description

// ,Tx as x_Tx // Tax related, to be ignored

// ,Inv.date as x_Inv_Date

  ,QTY

  ,[Gross Price]

// ,[Cash Disc.]

// ,[QDInv Rs/%]

// ,[TDInv Rs/%.] // always 0

  ,Sec.frt

// ,[C&FInv] // always 0

// ,Octroi // always 0

// ,BarrTax // always 0

// ,AGT // always 0

// ,EntryTax // always 0

  ,[Net Bill Price]

  ,Tax

  ,ExcPMT

  ,Exc%

  ,MRP

  ,[Basic Exc]

  ,[Edu Cess]

  ,[Sec&HECess]

  ,[Base Price]

// ,[QDAcrRs/%] // always 0

// ,[TDAcrRs/%] // always 0

// ,[ADAcrRs/%] // always 0

  ,PrivDisc

  ,OPAcom

  ,CACcom

  ,SPAcom

  ,[SecFrt Z1AF]

  ,[C&Facr]

// ,OctAcr // always 0

// ,BarTaxAcr // always 0

  ,AGTAcr

  ,UnincFrt

  ,NDPJ08Acr

  // ,Survey.chrgs // always 0

  // ,[Port Chrgs] // always 0

  ,[Can Inv ind]

resident DUMP;

store DSR into dsr_$(y).qvd;

drop tables DUMP, DSR;

Next TextFile;

1 Reply

Re: add new column in Qlikview converter

The new field should exist in the DUMP table or you should assign a value to it yourself:

,'SomeDefaultValueHere' as [Can Inv ind]


talk is cheap, supply exceeds demand
Community Browser