topic Re: Add a value in script in QlikView

Add a value in script

Wed, 30 Mar 2016 13:11:59 GMT

Hello,

how do I manage this?

Order number 0372813216 and position >000100 to position <000200 has to be marked with the same
characters as in pos 30-32(in this case 230) and the same with the positions >000200 until a new
order number comes. How do I do this in my script ? Today it looks like this :

if([@30:32]<>'',[@30:32],[@30:31]) as [ST], //SalesType

The source looks like this

0372800333 000200 0000014000

0372800333 000205 0000003600

0372813216 000100 0000028500 230

0372813216 000105 0000005700

0372813216 000110 0000014800

0372813216 000115 0000003600

0372813216 000200 0000028500 230

0372813216 000205 0000005700

0372813216 000210 0000014800

0372813216 000215 0000003600

0372816927 000100 0000002000

0372816927 000200 0000002000

0372816992 000100 0000009200

0372816992 000105 0000003600

0372816992 000200 0000009200

kindly

Håkan

Re: Add a value in script

sunny_talwar — Wed, 30 Mar 2016 13:28:38 GMT

May be this?

Table:

LOAD * Inline [

Field1, Field2, Field3, Field4

0372800333, 000200, 0000014000

0372800333, 000205, 0000003600

0372813216, 000100, 0000028500, 230

0372813216, 000105, 0000005700

0372813216, 000110, 0000014800

0372813216, 000115, 0000003600

0372813216, 000200, 0000028500, 230

0372813216, 000205, 0000005700

0372813216, 000210, 0000014800

0372813216, 000215, 0000003600

0372816927, 000100, 0000002000

0372816927, 000200, 0000002000

0372816992, 000100, 0000009200

0372816992, 000105, 0000003600

0372816992, 000200, 0000009200

];

FinalTable:

NoConcatenate

LOAD Field1,

Field2,

Field3,

If(Field1 = Peek('Field1'), Peek('Field4'), Field4) as Field4

Resident Table

Order By Field1, Field2;

DROP Table Table;

Re: Add a value in script

Wed, 30 Mar 2016 13:55:50 GMT

Excuse me, I didn´t tell that the source file I showed is only an

exerpt from a much bigger file so it has to be at the second last

line in this script:

Fakturor:
LOAD [@1:8] as [FaktPeriod],
     [@1:6] as [Period],
     [@1:4] & '-' & [@5:6] & '-' & [@7:8] as [InvoiceDate],
     [@9:9] as [FakturaTyp] ,
     [@10:18]as [FakturaKvantitet],
     if([@9:9]='F',[@10:18],0) as [DebetFakturaKvantitet],
     if([@9:9]='K',[@10:18],0) as [KredFakturaKvantitet],
     [@19:29] as [Pr00ZEBR],
     [@30:39] as [ZSEN],
     [@40:49] as [ZSE2],
     [@50:59] as [ZSE7],
     [@60:69] as [ZSED],
     [@70:79] as [ZSEM],
     [@80:89] as [ZSEA],
     [@90:99] as [ZSES],
     [@100:109] as [VPRS],
     [@110:117] as [OrgSäljare],
     if([@118:118]='L', ApplyMap ('Map_salesman1', [@127:134], 'SE999900'), ApplyMap ('Map_salesman2', [@127:134], 'SE999900')) as [Säljare],
     [@118:118] as [VaruSlag],
     [@119:126] as [Betalare],
     [@127:134] as [Beställare],
     [@135:149] as [KundHierarki],
     [@150:151] as [LandsKod],
     [@171:188] as [ProduktHierarki],
     [@171:172] as [ProdH_L1],
     [@173:174] as [ProdH_L2],
     if([@171:172]='11' ,[@173:174],'') as [GlasBrand],
     if([@171:172]='21' ,[@173:174],'') as [BågBrand],
     [@175:176] as [ProdH_L3],
     if([@175:176] ='00'and [@171:172]='12' ,previous([@175:176]),[@175:176]) as [ProdH_L3_C],
     [@177:177] as [ProdH_L4],
     [@178:179] as [ProdH_L5],
     [@180:181] as [ProdH_L6],
     [@182:183] as [ProdH_L7],
     [@184:184] as [ProdH_L8],
     [@185:188] as [ProdH_L9],
     [@171:179] as [ProdH9_L], // För Meta-data
     if([@171:172]='11' ,ApplyMap('Map_GlasNamn',[@175:176]&[@178:179]),if([@171:172]<>'11',[@216:253],'')) as [ProduktNamnG],
     if([@171:172]='11' ,[@175:176],'') as [GlasGrupp],
     if([@171:172]='12' or [@171:172]='13',[@171:172] & [@178:179]) as [SurchKey],
     if([@171:172]='13' or [@171:172]='14',[@171:172] & [@178:179]) as [ServiceKey],
     if([@171:172]='13' or [@171:172]='14',[@171:172] & [@175:176]) as [ServiceKeyL3],
     if([@171:172]='21' ,[@171:172] & [@175:176],'') as [SL],
     [@184:184] as [LagerRx],
     [@200:215] as [FakturaNr],
     [@216:253] as [FakturaText],
     if([@171:172]='21' ,left([@216:253],index([@216:253],'-')-1),'') as [BågText],
     if([@171:172]='21' and left([@216:253],4)='uvex' , mid([@216:253],index([@216:253],'.' )+2 ) ,'') as [UvexText],
     [@254:255] as [Diameter],
     [@256:263] as [OrderDatum],
     [@266:275] as [GlasKod],
     [@276:277] as [Diameter2],
     [@278:287] as [CoatingKod],
     [@288:291] as [FärgKod],
     [@292:309] as [MaterialNr],
     [@310:319] as [ZAZU],
     [@320:329] as [ZHDC],
     [@330:339] as [ZHDF],
     [@340:349] as [ZPZU],
     [@350:359] as [EloNr],
     [@360:365] as [EloPos],
     [@366:375] as [NettoVärde],
     [@376:385] as [MTP],
     [@386:391] as [CalcSchema],
     if([@9:9]='F',[@470:479],0) as [DebPris],
     if([@9:9]='K',[@470:479],0) as [KredPris],
     [@392:399] as [LeveransDatum],
     [@430:459] as [KommNr2],
     [@400:429] as [ZSEP],
     [@430:470] as [CuOrd],
     [@480:483] as [OrderReg],
     if([@484:486]<>'',[@484:486],[@484:485]) as [ST], //SalesType
     if([@19:29]> [@470:479],'X','') as Discount

FROM

[$(vInvoiceFileSourceData2016)ST_qvb_stat2016.txt]
(fix, codepage is 1252)
where ([@266:268]<>'GGW');

Håkan

Re: Add a value in script

sunny_talwar — Wed, 30 Mar 2016 14:01:00 GMT

Not sure I understand? does Field4 correspond to in your complete script?

Re: Add a value in script

Wed, 30 Mar 2016 14:10:02 GMT

Hello again,

I understand that I confuse you, in the first time I would only show the principle but I understand that I have to show the complete script and a very small part of the source file which can contain about 1 million lines.

this is what the complete lines look and are read by the script. thanks for taking your time.

See attached file.

kindly

Håkan