Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
xyz1
Contributor III

?

.

1 Solution

Accepted Solutions

Re: Cross table

Try this

FORECAST:
CrossTable (Month, For_Quantity,5) 
LOAD 'Sales' as Flag,
	"Dom/Exp",
    RGP_CODE,
	"State/Country" as state,
	Product as PRODUCT_CICS,
    "M 1/19" as Jan,
    "M 2/19" as Feb,
    "M 3/19" as Mar,
    "M 4/19" as Apr,
    "M 5/19" as May,
    "M 6/19" as Jun,
    "M 7/19" as Jul,
    "M 8/19" as Aug,
    "M 9/19" as Sep,
    "M 10/19" as Oct,
    "M 11/19" as Nov,
    "M 12/19" as Dec
FROM [lib://Forecasts/Locked_Fcst_20180727 -Indian Origin.xlsx]
(ooxml, embedded labels, table is Sheet1);

FINAL_FORECAST:
NoConcatenate
LOAD Flag,
	"Dom/Exp",
        RGP_CODE,
	state,
	PRODUCT_CICS,
        Month(Date#(Month, 'MMM')) as Month,
        For_Quantity
Resident FORECAST;

DROP TABLE FORECAST;

SALES:
LIB CONNECT TO 'PCSOFT';
Concatenate
LOAD Factory,
    month("DATE") as Month,
    "IM_CODE",
    PurgeChar("IM_CODE", '1234567890') as RGP_CODE,
    QTY as Sales_Quantity,
    state;
SQL SELECT *
FROM BI.dbo.Salepricing;
1 Reply

Re: Cross table

Try this

FORECAST:
CrossTable (Month, For_Quantity,5) 
LOAD 'Sales' as Flag,
	"Dom/Exp",
    RGP_CODE,
	"State/Country" as state,
	Product as PRODUCT_CICS,
    "M 1/19" as Jan,
    "M 2/19" as Feb,
    "M 3/19" as Mar,
    "M 4/19" as Apr,
    "M 5/19" as May,
    "M 6/19" as Jun,
    "M 7/19" as Jul,
    "M 8/19" as Aug,
    "M 9/19" as Sep,
    "M 10/19" as Oct,
    "M 11/19" as Nov,
    "M 12/19" as Dec
FROM [lib://Forecasts/Locked_Fcst_20180727 -Indian Origin.xlsx]
(ooxml, embedded labels, table is Sheet1);

FINAL_FORECAST:
NoConcatenate
LOAD Flag,
	"Dom/Exp",
        RGP_CODE,
	state,
	PRODUCT_CICS,
        Month(Date#(Month, 'MMM')) as Month,
        For_Quantity
Resident FORECAST;

DROP TABLE FORECAST;

SALES:
LIB CONNECT TO 'PCSOFT';
Concatenate
LOAD Factory,
    month("DATE") as Month,
    "IM_CODE",
    PurgeChar("IM_CODE", '1234567890') as RGP_CODE,
    QTY as Sales_Quantity,
    state;
SQL SELECT *
FROM BI.dbo.Salepricing;