Weird Join Question

Anonymous — Wed, 15 Mar 2017 22:38:32 GMT

this is a 3 section question and i think fixing any of the 3 section will fix my issue

so below i have this table

basically its color and item code then a hashcode then image location

the issue is i have multiple location and i need to try to narrow it down to one..

there are 3 ways -- 2 of them in qv and not sure how to do it

1 the formula used to pull the location should be

=firstsortedvalue(swlocation,

- aggr(

sum(

{$<DTL.Flag_OrderStatus={1}>}DTL.Qty*DTL.Flag_OrderStatus)

,PD.Style,swlocation))

which should work because of the first sorted.. but it doesnt seem to work not sure why..

the other way is the join where we got this table.. i am joining 2 tables to get this one..

if somehow i can do max or something

select distinct i.itemCode, color =col.dimensionLong,

bcItemCode =reverse(substring(reverse(i.itemcode),1,4)),

itemDesc = i.itemDesc,

BCColorCode =isnull(bc_col.color_code,''),

Swatch =isnull(img.ImageUrl,'')

from dbo.VItem i

join .VItemVariant iv

on i.itemId = iv.itemId

join VDimension col

on col.dimensionId =iv.colorId

left join.dbo.zzxcolrr bc_col

on bc_col.color_group + bc_col.nrf_color =col.dimensionCode+col.nrf collate Chinese_PRC_CI_AS

left join .VImage img

on img.classId =iv.itemVariantId

and img.ObjectType ='ItemVariant'

and img.imageType ='Standard Image'

where i.Category ='Fabric'

or i.Category = 'Yarn'

and i.SeasonId > 429542

;

join(FabImages)

load

bcItemCode as BCItemCode,

BCColorCode as BCColorcode,

Swatch as swlocation,

hash128(bcItemCode,BCColorCode) as fabcolor

resident temp14;

not sure whats the easiest

thanks

topic Weird Join Question in QlikView