topic Re: Problem with crosstable in QlikView

Problem with crosstable

Tue, 19 May 2015 16:33:54 GMT

I have two separate files and I'm trying to create one crosstable that will contain information from both tables, however my value_over appears to have no information but I know that there are records that should be showing up. Here is my load script; any suggestions as to why my value_over is not picking up the data????

bench_table:

LOAD FYEAR,

FPERIOD,

text(ITEM) as ITEM,

NO_UNITS,

SALES_AMNT

FROM

[..\QVD\bench.qvd]

(qvd);

bench_override:

LOAD

text("T$ITEM") as ITEM,

"T$SQTY" as SHIP_QTY_OVR,

"T$GSAL" as GROSS_SALES_OVR;

SQL Select * from table_1;

BM_Temp:

CrossTable(Measure,Value,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

FPERIOD,

if (sum(NO_UNITS)<>0,sum(NO_UNITS),0) as [Shipment Qty],

if (sum(SALES_AMNT)<>0,sum(SALES_AMNT),0) as [Gross Sales]

Resident bench_table

where ITEM > '' or Len(Trim(ITEM))>0

Group by ITEM, FYEAR, FPERIOD;

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM),

FYEAR,

FPERIOD,

if (sum(SHIP_QTY_OVR)<>0,sum(SHIP_QTY_OVR),0) as [Shipment Qty],

if (sum(GROSS_SALES_OVR)<>0,sum(GROSS_SALES_OVR),0) as [Gross Sales]

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Concatenate(BM_Temp)

LOAD * Resident BM_Override_Temp;

Drop Table BM_Override_Temp;

When I look at the table for an item it doesn't show any value for value_ovr but of course there's a value in there because SHIP_QTY_OVR is showing something.

Re: Problem with crosstable

swuehl — Tue, 19 May 2015 17:11:43 GMT

Could be just a copy & paste issue, but shouldn't it be like

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

...

Re: Problem with crosstable

Tue, 19 May 2015 17:28:15 GMT

not sure what you are asking.

here is my load script; basically I want the crosstable to have the item, measure, value, and value_ovr so that I know if there's an override value for that item

bench_table:

LOAD FYEAR,

FPERIOD,

text(ITEM) as ITEM,

NO_UNITS,

SALES_AMNT

FROM

[..\QVD\bench.qvd]

(qvd);

bench_override:

LOAD

text("T$ITEM") as ITEM,

"T$SQTY" as SHIP_QTY_OVR,

"T$GSAL" as GROSS_SALES_OVR;

SQL Select * from table_1;

BM_Temp:

CrossTable(Measure,Value,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

FPERIOD,

if (sum(NO_UNITS)<>0,sum(NO_UNITS),0) as [Shipment Qty],

if (sum(SALES_AMNT)<>0,sum(SALES_AMNT),0) as [Gross Sales]

Resident bench_table

where ITEM > '' or Len(Trim(ITEM))>0

Group by ITEM, FYEAR, FPERIOD;

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM),

FYEAR,

FPERIOD,

if (sum(SHIP_QTY_OVR)<>0,sum(SHIP_QTY_OVR),0) as [Shipment Qty],

if (sum(GROSS_SALES_OVR)<>0,sum(GROSS_SALES_OVR),0) as [Gross Sales]

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

Concatenate(BM_Temp)

LOAD * Resident BM_Override_Temp;

Drop Table BM_Override_Temp;

Re: Problem with crosstable

swuehl — Tue, 19 May 2015 17:45:32 GMT

I'm asking myself if you should better alias your field called text(ITEM) to ITEM.

Do you have an additional field 'text(ITEM)' when looking at the table BM_Temp? I believe you want to have this field instead be concatenated to ITEM, so you need an alias here, too.

BM_Override_Temp:

CrossTable(Measure, Value_Ovr,3)

LOAD

text(ITEM) as ITEM,

FYEAR,

FPERIOD,

if (sum(SHIP_QTY_OVR)<>0,sum(SHIP_QTY_OVR),0) as [Shipment Qty],

if (sum(GROSS_SALES_OVR)<>0,sum(GROSS_SALES_OVR),0) as [Gross Sales]

Resident bench_override

Group By ITEM, FYEAR, FPERIOD;

[If you use an expression like text(ITEM) in your script, QV will use the expression also as field name unless you use AS to state the alias]

Re: Problem with crosstable

Wed, 20 May 2015 18:30:09 GMT

I see the error. I thought I had that in place but I guess I overlooked it. That fixed the problem.