topic Need help - combine left and right in script in QlikView

Need help - combine left and right in script

Thu, 03 Jan 2013 08:21:02 GMT

Hello again,

i got a huge problem that i cant solve on my own.

Situation: There has been some changes(law) that fakture numbers now look different.

Old Nr: 05-12345 (Left(INVOICEID,2))

new Nr: 12345 - 05 - 1 (Right(INVOICEID, 4))

My script looks like this:

WHERE Left(INVOICEID,2) <>'06' and Left(INVOICEID,2) <>'ER' and Left(INVOICEID,2) <>'15' and Left(INVOICEID,2) <>'09' and Left(INVOICEID,2) <>'12' and Left(INVOICEID,2) <>'23' and Left(INVOICEID,2) <>'22' and Left(INVOICEID,2) <>'24' and Left(INVOICEID,2) <>'25';

drop Table ReLinije;

All faktures that beginn with those numbers are not to be considered in my stats and will be dropped from the resident!.

Problem: Old fakture number will not be changed but i need to compare f. ex. 2012 with 2013 and so on.

What i need/hope for help: How can i combine Left and Right formula in one script, so that All faktures before 2013 fall under LEFT formula and all following on RIGHT?

I hope four your help and thank you in advance.

And a Happy new Year 😃

Re: Need help - combine left and right in script

Thu, 03 Jan 2013 08:26:30 GMT

Hello,

You can load old factures in a first time, and the new with the key word CONCATENATE after. Both with their own restrictions.

Amand Dupretz

Re: Need help - combine left and right in script

Anonymous — Thu, 03 Jan 2013 08:29:55 GMT

You could load you table twice. The first time with all the rows but setting a flag of whether to include, as follows:

If(Match(Left(INVOICEID,2),'06','ER','15','09','12','23','22','24','25'),1) AS Exclude

Then the second load could have awhere clause to exclude those matching this field:

...

WHERE Exclude = 1;

Jonathan

Re: Need help - combine left and right in script

Thu, 03 Jan 2013 08:36:26 GMT

I forgot to mention, that the new facture number changes like this: 1 - 05 - 1, 2 - 05 - 1, etc...

Here is the whole one:

ReLinije:

LOAD CJ_AK,

CJ_DOG,

CJ_MIN,

COMMISSSALESGROUP,

CUSTACCOUNT,

DIM10,

DIM5,

DIM6_JOUR,

DIM6_TRANS,

DLVMODE,

GROSPROFIT,

INVENTLOCATIONID,

INVOICEDATE,

Mesec,

Godina,

INVOICEID,

ITEMID,

LINEAMOUNT,

QTY,

SALESDISCPERCENT,

SALESID, //br specke

STOCKVALUE,

STOCKVALUE2

FROM

Y:\Linije.qvd (qvd);

Linije:

NoConcatenate LOAD

CJ_AK,

CJ_DOG,

CJ_MIN,

COMMISSSALESGROUP,

CUSTACCOUNT,

DIM10,

DIM5,

DIM6_JOUR,

DIM6_TRANS,

DLVMODE,

GROSPROFIT,

INVENTLOCATIONID,

INVOICEDATE,

Left(INVOICEDATE,10) as [Datum_fakture], // TEST

day(INVOICEDATE) as Dan,

MonthName(INVOICEDATE) as ImeMjeseca,

Mesec as Mjesec,

Godina,

INVOICEID,

Left(INVOICEID,2) as [Predbroj fakture],

text(PurgeChar(text(ITEMID), ' ')) as ITEMID,

ITEMID as ITEMID1,

LINEAMOUNT,

QTY,

SALESDISCPERCENT,

SALESID, //br specke

STOCKVALUE,

STOCKVALUE2,

LINEAMOUNT/QTY as [Prodajna cijena]

RESIDENT ReLinije

drop Table ReLinije;

Can you tell me where to put your suggestions?

Re: Need help - combine left and right in script

Thu, 03 Jan 2013 09:53:02 GMT

Can somebody tell me the formula(in script) for:

If INVOICEDATE <01.01.2013 where left(Invoiceid, 2) <> '06',...

If invoicedate >31.12.2012 where right(Invoiceid,5) <> '-06-1'...

Drop table relinije

I need to combine them somehow that Linije.qvd and relevant data from 2012 and2013 remain in the table