topic Re: Easier syntax solution ... ? in QlikView

Easier syntax solution ... ?

mato32188 — Wed, 13 Aug 2014 09:02:51 GMT

Hi there,

load works well, but I would like to know whether

exists any solution how to make this script syntax EASIER or not?:

StructureProd:

Hierarchy(UpPrOrderNum, PrOrderNum, ProdOrder) LOAD

UpPrOrderNum ,

PrOrderNum,

PrOrderNum as ProdOrder

FROM

..\Table1.qvd

(qvd);

MAP_UPITEMCODE:

Mapping LOAD

PrOrderNum as UpPrOrderNum,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE1:

Mapping LOAD

PrOrderNum as ProdOrder1,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE2:

Mapping LOAD

PrOrderNum as ProdOrder2,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE3:

Mapping LOAD

PrOrderNum as ProdOrder3,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE4:

Mapping LOAD

PrOrderNum as ProdOrder4,

ItemCode

FROM

..\Table1.qvd

(qvd);

;

MAP_ITEMCODE5:

Mapping LOAD

PrOrderNum as ProdOrder5,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE6:

Mapping LOAD

PrOrderNum as ProdOrder6,

ItemCode

FROM

..\Table1.qvd

(qvd);

MAP_ITEMCODE7:

Mapping LOAD

PrOrderNum as ProdOrder7,

ItemCode

FROM

..\Table1.qvd

(qvd);

StructureItem:

LOAD *, ApplyMap('MAP_UPITEMCODE', UpPrOrderNum, null()) as UpItemCode,

ApplyMap('MAP_ITEMCODE', PrOrderNum, null()) as ItemCode,

ApplyMap('MAP_ITEMCODE1', ProdOrder1, null()) as ItemCode1,

ApplyMap('MAP_ITEMCODE2', ProdOrder2, null()) as ItemCode2,

ApplyMap('MAP_ITEMCODE3', ProdOrder3, null()) as ItemCode3,

ApplyMap('MAP_ITEMCODE4', ProdOrder4, null()) as ItemCode4,

ApplyMap('MAP_ITEMCODE5', ProdOrder5, null()) as ItemCode5,

ApplyMap('MAP_ITEMCODE6', ProdOrder6, null()) as ItemCode6,

ApplyMap('MAP_ITEMCODE7', ProdOrder7, null()) as ItemCode7

Resident StructureProd

;

DROP Table StructureProd

;

ItemCode1 .. ItemCode7 maximum possible range generated by hierarchy function so far

Thank you in advance

Re: Easier syntax solution ... ?

Wed, 13 Aug 2014 09:30:03 GMT

Hi Martin,

First of all, I might have missed something, but why use multiple Mapping Tables if you are loading the same fields from the same table ?

Regards,

Nicolas

Re: Easier syntax solution ... ?

mato32188 — Wed, 13 Aug 2014 09:56:53 GMT

Hello Nicolas,

hierarchy function creates fields named ProdOrder1 ... ProdOrder7 and I wanted to match those ProdOrders with ItemCodes from 1 to 7 to make the same hierarchy for Items. I am not sure, if such method is right, but it works.

Re: Easier syntax solution ... ?

Wed, 13 Aug 2014 11:36:46 GMT

As far as I know, you don't need to have the exact same name for both fields when you use an ApplyMap.

The ApplyMap function compares the value with available values in the first column of the mapping table. Then, it returns the corresponding value in the second column.

Sadly, I have to recognize that I'm not a specialist in the use of the Hierarchy function, but - in my opinion - the use of only one mapping table is a first lead of improvement for this script.

Re: Easier syntax solution ... ?

sudeepkm — Wed, 13 Aug 2014 11:55:41 GMT

have you tried creating a hierarchy for ItemCodes using the similar script. then both tables may be associated based on the prod order num.

Re: Easier syntax solution ... ?

mato32188 — Wed, 13 Aug 2014 13:05:48 GMT

Thank you Nicolas for advice. You are right.