topic Re: create new dummy records using script in QlikView

create new dummy records using script

sadiaasghar — Wed, 25 Nov 2020 16:16:04 GMT

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]);

Re: create new dummy records using script

sadiaasghar — Wed, 14 Oct 2020 12:03:42 GMT

@sunny_talwar @Chanty4u Please have a look.

Re: create new dummy records using script

sadiaasghar — Wed, 14 Oct 2020 19:23:18 GMT

@Kushal_Chawda Please help me with this.

Re: create new dummy records using script

Kushal_Chawda — Wed, 14 Oct 2020 23:18:04 GMT

@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';

Re: create new dummy records using script

sadiaasghar — Thu, 15 Oct 2020 03:12:58 GMT

@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

Re: create new dummy records using script

sadiaasghar — Thu, 15 Oct 2020 03:54:35 GMT

I am attaching excel sheet for reference

Re: create new dummy records using script

Kushal_Chawda — Thu, 15 Oct 2020 09:34:06 GMT

@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$);

Re: create new dummy records using script

Gabriel — Thu, 15 Oct 2020 09:48:28 GMT

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

Re: create new dummy records using script

sadiaasghar — Tue, 20 Oct 2020 10:28:04 GMT

thank you all Cross Join works in my case.