create new dummy records using script

[sadiaasghar]

I fetch daily sales data in SharePoint DATA source and I want to fetch data for a particular company ABC from SAP. So for that purpose I want to create dummy records for each date and Product for Company ABC in my DailySalesVolume table. like I have 2 distinct dates and 8 distinct products in data source so I want to dynamically create 16 records for Company ABC using loop. How can I achieve this in qlikview.

DailySalesVolume:
LOAD ows_LinkTitle as Company,
if(ows_Product='MS','PMG',if(ows_Product='KERO','SKO',if(ows_Product='JP-1','JET-A1',if(ows_Product='FO','F. Oil',ows_Product)))) as PRODUCT,
Date(ows_Date,'DD/MM/YYYY') as AppDate,
Month(ows_Date) as AppMonth,
Year(ows_Date) as AppYear,
num(Month(ows_Date)) as MonthNo,
//Date(yearstart(ows_Date,0,7),'YYYY') as FY,
if(ows_Volume=' ' or ows_Volume='0' or ows_Volume= '-' ,0,ows_Volume) as AppVolume,
%Key_xml_98C49F546A18E856 // Key to parent table: xml
FROM [http://site/subsite/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=0&RowLimit=0&List={83908779%2D467e%2D41a3%2Dbe3b%2Dfa23cb0f90ef}&View={47ED70E2-0F10-45C5-A294-1BE1866C1E52}] (XmlSimple, Table is [xml/data/row]);

[sadiaasghar]

@sunny_talwar @Chanty4u Please have a look.

[sadiaasghar]

@Kushal_Chawda  Please help me with this.

[Kushal_Chawda]

@sadiaasghar  you can try cross join of Date & Product like below

DailySalesVolume:
LOAD distinct Date(ows_Date,'DD/MM/YYYY') as AppDate
FROM [http://site/subsite/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=0&RowLimit=0&List={83908779%2D467e%2D41a3%2Dbe3b%2Dfa23cb0f90ef}&View={47ED70E2-0F10-45C5-A294-1BE1866C1E52}] (XmlSimple, Table is [xml/data/row])
where Company='ABC';

join
LOAD distinct if(ows_Product='MS','PMG',if(ows_Product='KERO','SKO',if(ows_Product='JP-1','JET-A1',if(ows_Product='FO','F. Oil',ows_Product)))) as PRODUCT
FROM [http://site/subsite/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=0&RowLimit=0&List={83908779%2D467e%2D41a3%2Dbe3b%2Dfa23cb0f90ef}&View={47ED70E2-0F10-45C5-A294-1BE1866C1E52}] (XmlSimple, Table is [xml/data/row])
where Company='ABC';

[sadiaasghar]

@Kushal_Chawda Company ABC is not present in my intranet data. I want to insert Company ABC dummy data for each date and product. I have two distinct dates and 8 distinct product. As per my logic 2 loops will be created outer loop for dates and inner loop for product where 

I want to load 

'ABC' as Company,

'0' as Volume,

All the products as product,

Date as AppDate

@Kushal_Chawda Please suggest a/c to this logic

[sadiaasghar]

I am attaching excel sheet for reference

[Kushal_Chawda · Accepted solution]

@sadiaasghar  try below

Data:
LOAD Distinct AppDate, 
     'ABC' as Company
FROM
[salesdata.xls]
(biff, embedded labels, table is Sheet1$);

join(Data)
LOAD Distinct
     0 as AppVolume,
     PRODUCT
FROM
[salesdata.xls]
(biff, embedded labels, table is Sheet1$);

[Gabriel]

Hi,

If I understand your question. You have 2 date and 8 distinct product  and you want to create dummy records of 16 from the dates and products for ABC company.

Then I think you have to do below

tmpDate_Prod:

LOAD  DISTINCT AppDate FROM .......;

JOIN (tmpDate_Prod)

LOAD DISTINCT App_ProductFieldName FROM .......;

The above will give you your desired result

Let me know if you need help

[sadiaasghar]

thank you all Cross Join works in my case.