Script - Replicating Existing Tables - QV v8.5

[#1]

Hi,

I am trying to replicate an existing table in a new table.

When creating the new table, I cannot evaluate to new fields that have been created ("zzNoSpend" & "zzNoVolume")

Directory:
LOAD Material,
 Euros,
 QtyKG,
 CalYearMonth,
 if(Euros = 0 and QtyKG <> 0, 1, 0) AS NoVolume,
 if(QtyKG = 0 and Euros <> 0, 1, 0) AS NoSpend,
 if(left(Material,8) = 'Non Item',1,0) as NonItem,
 if(left(Material,4) = 'TEMP',1,0) as Temp
FROM [Dirty Data Example data2.xls] (biff, embedded labels, table is Sheet1$);

//======================================================================================
NewTable: // trying to clone the above table, and return same results
Load
Material,
Euros as zzEuros, // duplicate fields prefixed with 'zz'
QtyKG as zzQtyKG, // duplicate fields prefixed with 'zz'
CalYearMonth as zzCalYearMonth
Resident Directory;
 Join(NewTable)
Load
Material,
if(zzEuros = 0 and zzQtyKG <> 0, 1, 0) AS zzNoVolume, // issues with the inclusion of the following fields in formulas 'zzEuros' and 'zzQtyKG'
if(zzQtyKG = 0 and zzEuros <> 0, 1, 0) AS zzNoSpend,
if(left(Material,8) = 'Non Item',1,0) as zzNonItem,
if(left(Material,4) = 'TEMP',1,0) as zzTemp
Resident NewTable;

by using "join" above, fields "zzNoVolume" and "zzNoSpend" are recognised fields, and calculate correctly in the "IF" statements in the above Join section. However, my spend volume values have doubled.

by removing the join statement, and entering my if statements in the section above, the "zzNoVolume" and "zzNoSpend" fields are not recognised.

Anyone have any suggestions?

Please advise,

Kind regards,

Rich

[#2]

with Excel source data

[matt_crowther]

Rich,

Not 100% what the aim is but attached is a 'dual-table-results-are-the-same' solution.

Hope it helps?!?

All the best,

Matt - Visual Analytics Ltd

[matt_crowther · Accepted solution]

Rich,

Apologies the first post was junk - attached this time is one that doesn't drop half the fields!

Hope that helps,

Matt - Visual Analytics Ltd

[rwunderlich]

Your lines are doubling because you are joining on the single field "Material" and there are two rows for each Material in your source table. When adding fields via join, use LOAD * DISTINCT to make sure all fields participate in the join,

LEFT Join(NewTable)
Load DISTINCT *,
if(zzEuros = 0 and zzQtyKG <> 0, 1, 0) AS zzNoVolume,
if(zzQtyKG = 0 and zzEuros <> 0, 1, 0) AS zzNoSpend,
if(left(Material,8) = 'Non Item',1,0) as zzNonItem,
if(left(Material,4) = 'TEMP',1,0) as zzTemp

Resident NewTable;