Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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$);
@sunny_talwar @Chanty4u Please have a look.
@Kushal_Chawda Please help me with this.
@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';
@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
I am attaching excel sheet for reference
@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$);
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
thank you all Cross Join works in my case.