How to write a view or query to get dummy records ?

[suvechha_b]

Hi All,

I got something like the above table. product_key = 312 is having forecast value as 0 for the whole fiscal_year 2016 from 1-12.

How , to create dummy records - such as the same product_key will have zero (0) for the whole fiscal_year 2016 from 1-12 for "Amount Type " - Invoice . If , there is no records for "Amount Type " - Invoice.

Note : In my Qlikview,

I need values for both "Amount Type" - Forecast and Invoice for every product key and fiscal period.

At present , my Qlikview is having only records of "Amount Type" - Forecast for one product_key=312.

Thanks,

[effinty2112]

Hello,

          Instead of adding a lot of extra rows why not replace the field 'Qty' with one called 'Forecast' and add a field 'Invoice' and drop the field called 'Amount Type'.

Kind regards

Andrew

[suvechha_b]

The Qty might have data in it for other product.

If , the Qty is not having data , I want to create dummy records with zero . for Amount Type - forecast and Invoice.

[Anonymous]

Hi suveccha,

I suggest to create a new flag:

ForecastQty:

LOAD

     fiscal_year,

     product_key,

     [Amount Type],

     Sum(Qty)          as TotalQty

RESIDENT YourTable

GROUP BY

fiscal_year,

     product_key,

     [Amount Type];

LEFT JOIN (YourTable)

LOAD

     fiscal_year,

     product_key,

     [Amount Type],

     1                    as DummyRecord

WHERE [Amount Type] = 'Forecast' and TotalQty = 0;

On your expressions {<DummyRecord = {1}>} to identify Dummy records, or {<DummyRecord -= {1}>} for not dummy records.

Regards!