Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Aspiring_Developer
Creator III
Creator III

Crosstable to generate date while loading data in qlik

Hi Team,

I have the below data set :-

VLS Global                                                            
                                                            
CustomersApril-19May-19June-19July-19August-19September-19October-19November-19December-19January-20February-20March-20
NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Unofficial Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)NOK parts in customer database - totalReal NOK parts manufactured -  totalNumber of official Written concern Total number Alert + Written concernVolumes shipped (external sales only)
Brazil334139,780000118,540000114,940010116,0803603601117,550000114,400000013,560000015,15000006,720000115,7220000000000
Bulgaria002266,300000066,247001158,524222231,413000042,678000055,928000056,031000067,833131132,52992192331,21,3950000000000
India Pune 44823378,22024772281,29662914475,56742302276,70141882264,34851553466,40823041166,05525941167,96365532279,107121,149331,29,0070000000000
Italy2225222590,727266266881,60,4549999881,29,1819494331,22,7020120548,7599653101,57,68014545141,06,1001601590,640010166,798079141,89,8600000000000
Mexico601,991293,98,36561,2165185,03,68969562114,41,1842875293,97,01913981193,82,42522170154,08,460263556184,26,5881255194,04,8692151283,12,55312152253,52,5110000000000
Morocco0950025,4680680032,5062340249,5531551159,620090028,16801850045,7334704468,1250420053,3437942256,0740400081,4830000000000
Novy Jicin2748115349,33,55621988215310,17,3641363510378,49,793121,0087596,40,068134566176,28,02187958309,51,84386777419,57,24571,2617478,57,426104776376,59,0191572,88411378,75,7790000000000
Rychvald3453141,88,5616633232,43,94941484152,15,828101846241,89,9293203131,65,30913549332,62,372105610273,22,98512315183,05,4243583112,30,73461676273,23,6160000000000
Romania00112,72,355010023,19,04002012,61,60701013,18,17000001,15,039714902343,19,73022222,16,98103022,72,21300001,08,60400002,73,6420000000000
Poland000000000000000000000000000000000000000031292293,248018202614,9230000000000
Turkey664421,49,1753623871223,23,812664417,69,5653193194619,77,00901781117,91,943183002826,33,69681324621,84,6762844361726,43,94916274622,88,8366173423,99,2650000000000
Vietnam111178,6991072097772,51330302286,870222279,711443378,405111184,290111170,8951951951187,071772387,39511111,14,3580000000000
VTYC CQ38331,50,985000086,8397174471,51,33349241,60,1706114391,03,70800001,88,9432312312,23,37388442,61,95122222,28,203000000000000000
VTYC CZ55221,02,48604001,09,470131384,792394997,60924221,00,14602021,17,62601011,05,19411111,47,8491171694,689000000000000000
India Chennai000000000000000000000000000000000000000000000000000000000000
 000000000000000000000000000000000000000000000000000000000000
 000000000000000000000000000000000000000000000000000000000000
 000000000000000000000000000000000000000000000000000000000000
Europe (NJ,RY,PL, Mor.) 30621184811,47,585271,119247612,93,8191981714540231,24714848,89,617164859308,21,498211,034176312,59,94822803217213,48,355191,334126512,16,1932375813799,49,0751633,273179012,95,8010000000000
US (Brazil+ Mexico)632,0253124,08,14561,2165195,22,2296956212028762104,13,0993617582203,99,97522170164,22,860263556184,40,1481255194,20,0192151283,19,27312152263,68,2330000000000
2WSL2935303326,57,256735872161929,42,06613513715160417418111425,29,00541944920,76,8247421,26892332,51,32425189122326,34,68322470182531,61,706243871125,84,1629928981230,98,5200000000000
China 813552,70,85804001,96,309817751007186132,57,77981185112,03,85402023,06,5692322323,28,56799554,09,800319383,22,892000000000000000
India (Pune+ chennai)44823378,22024772281,296629144042302276,70141882264,34851553466,40823041166,05525941167,96365532279,107121,149331,29,0070000000000
 000000000000000000000000000000000000000000000000000000000000
TOTAL ACTUAL1343,1735910145,44,6777703,6884711650,35,7191742,378409641,88,7374532,7893512341,66,2013931,743227235,66,4997702,6762910853,07,109771,6834214648,17,8082552,8932710552,75,681581,5192710842,54,5092864,8633011148,91,5610000000000

 

I wish to get the below output:-

 NOK parts in customer database - totalReal NOK parts manufactured - totalNumber of official Written concernTotal number Alert + Unofficial Written concern
Apr-19296981298222224
May-19153732933323035
Jun-1942568954922919
Jul-19109669840129523
Aug-19110489616920188
Sep-19145504546420350
Oct-1916349871830304
Nov-1948548511819903
Dec-1914357634625385
Jan-2058994613322692
Feb-20#DIV/0!#DIV/0!#DIV/0!#DIV/0!
Mar-20#DIV/0!#DIV/0!#DIV/0!#DIV/0!

 

Can anyone please help ?

Thanks in advance

1 Solution

Accepted Solutions
Aspiring_Developer
Creator III
Creator III
Author

Hi Team,

I have solved it my self.

I requested the user to make a slight change in the headers name and named it as "NOK parts in customer database - total_Apr19", "Number of official Written concern_May19", "Total number Alert + Unofficial Written concern_June19","Volumes shipped (external sales only)_July19", etc.

1) Simply load the data in QV:-

Data:
LOAD F1 as Customer,

[NOK parts in customer database - total_Apr19],
[Real NOK parts manufactured - total_Apr19],
[Number of official Written concern_ Apr19],
[Total number Alert + Written concern_Apr19],
[Volumes shipped (external sales only)_Apr19],

[NOK parts in customer database - total_May19],
[Real NOK parts manufactured - total_May19],
[Number of official Written concern_May19],
[Total number Alert + Written concern_May19],
[Volumes shipped (external sales only)_May19],

[NOK parts in customer database - total_Jun19],
[Real NOK parts manufactured - total_Jun19],
[Number of official Written concern_Jun19],
[Total number Alert + Written concern_Jun19],
[Volumes shipped (external sales only)_Jun19],

[NOK parts in customer database - total_Jul19],
[Real NOK parts manufactured - total_Jul19],
[Number of official Written concern_Jul19],
[Total number Alert + Written concern_Jul19],
[Volumes shipped (external sales only)_Jul19],

[NOK parts in customer database - total_Aug19],
[Real NOK parts manufactured - total_Aug19],
[Number of official Written concern_Aug19],
[Total number Alert + Written concern_Aug19],
[Volumes shipped (external sales only)_Aug19],

[NOK parts in customer database - total_Sep19],
[Real NOK parts manufactured - total_Sep19],
[Number of official Written concern_Sep19],
[Total number Alert + Written concern_Sep19],
[Volumes shipped (external sales only)_Sep19],

[NOK parts in customer database - total_Oct19],
[Real NOK parts manufactured - total_Oct19],
[Number of official Written concern_Oct19],
[Total number Alert + Written concern_Oct19],
[Volumes shipped (external sales only)_Oct19],

[NOK parts in customer database - total_Nov19],
[Real NOK parts manufactured - total_Nov19],
[Number of official Written concern_Nov19],
[Total number Alert + Written concern_Nov19],
[Volumes shipped (external sales only)_Nov19],

[NOK parts in customer database - total_Dec19],
[Real NOK parts manufactured - total_Dec19],
[Number of official Written concern_Dec19],
[Total number Alert + Written concern_Dec19],
[Volumes shipped (external sales only)_Dec19],

[NOK parts in customer database - total_Jan20],
[Real NOK parts manufactured - total_Jan20],
[Number of official Written concern_Jan20],
[Total number Alert + Written concern_Jan20],
[Volumes shipped (external sales only)_Jan20],

[NOK parts in customer database - total_Feb20],
[Real NOK parts manufactured - total_Feb20],
[Number of official Written concern_Feb20],
[Total number Alert + Written concern_Feb20],
[Volumes shipped (external sales only)_Feb20],

[NOK parts in customer database - total_Mar20],
[Real NOK parts manufactured - total_Mar20],
[Number of official Written concern_Mar20],
[Total number Alert + Written concern_Mar20],
[Volumes shipped (external sales only)_Mar20]
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report\OneDrive_2020-02-11\MOR and Quality Improvement Plan, FY2020\1. Global File\ZZ Global January 2019.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [To be deleted -Vaishali])
where RowNo()<25;

 

2) Create Calculative Dimensions and store it into QVD:-

Temp:
Load
Customer,
//official Customer PPM

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

([NOK parts in customer database - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as OfficialCustomerPPM_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([NOK parts in customer database - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as OfficialCustomerPPM_May19,


if([Volumes shipped (external sales only)_Jun19]=0,'0',

( [NOK parts in customer database - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as OfficialCustomerPPM_Jun19,


if([Volumes shipped (external sales only)_Jul19]=0,'0',

( [NOK parts in customer database - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as OfficialCustomerPPM_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

( [NOK parts in customer database - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as OfficialCustomerPPM_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

( [NOK parts in customer database - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as OfficialCustomerPPM_Sep19,


if([Volumes shipped (external sales only)_Oct19]=0,'0',

( [NOK parts in customer database - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as OfficialCustomerPPM_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

( [NOK parts in customer database - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as OfficialCustomerPPM_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

( [NOK parts in customer database - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as OfficialCustomerPPM_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

( [NOK parts in customer database - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as OfficialCustomerPPM_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

( [NOK parts in customer database - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as OfficialCustomerPPM_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

( [NOK parts in customer database - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as OfficialCustomerPPM_Mar20,

 

//Site PPM Performance
if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Real NOK parts manufactured - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as SitePPMPerformance_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Real NOK parts manufactured - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as SitePPMPerformance_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Real NOK parts manufactured - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as SitePPMPerformance_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Real NOK parts manufactured - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as SitePPMPerformance_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

([Real NOK parts manufactured - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as SitePPMPerformance_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

([Real NOK parts manufactured - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as SitePPMPerformance_Sep19,

if([Volumes shipped (external sales only)_Oct19]=0,'0',

([Real NOK parts manufactured - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as Site_PPM_Performance_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

([Real NOK parts manufactured - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as SitePPMPerformance_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

([Real NOK parts manufactured - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as SitePPMPerformance_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

([Real NOK parts manufactured - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as SitePPMPerformance_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

([Real NOK parts manufactured - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as SitePPMPerformance_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

([Real NOK parts manufactured - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as SitePPMPerformance_Mar20,

 


//Official Customer IPB

if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Number of official Written concern_ Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as OfficialCustomerPB_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Number of official Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as OfficialCustomerIPB_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Number of official Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as OfficialCustomerIPB_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Number of official Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as OfficialCustomerIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Number of official Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as OfficialCustomerIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Number of official Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as Official_Customer_IPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Number of official Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as OfficialCustomerIPB_Oct19,

if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Number of official Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as OfficialCustomerIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Number of official Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as OfficialCustomerIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Number of official Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as OfficialCustomerIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Number of official Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as OfficialCustomerIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Number of official Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as OfficialCustomerIPB_Mar20

 

,
//Site Performace IPB

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

( [Total number Alert + Written concern_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as SitePerformanceIPB_Apr19,

if( [Volumes shipped (external sales only)_May19]=0,'0',

( [Total number Alert + Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as SitePerformanceIPB_May19,


if( [Volumes shipped (external sales only)_Jun19]=0,'0',

( [Total number Alert + Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as SitePerformanceIPB_Jun19,


if( [Volumes shipped (external sales only)_Jul19]=0,'0',

( [Total number Alert + Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as SitePerformanceIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Total number Alert + Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as SitePerformanceIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Total number Alert + Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as SitePerformanceIPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Total number Alert + Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as SitePerformanceIPB_Oct19,


if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Total number Alert + Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as SitePerformanceIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Total number Alert + Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as SitePerformanceIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Total number Alert + Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as SitePerformanceIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Total number Alert + Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as SitePerformanceIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Total number Alert + Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as SitePerformanceIPB_Mar20

Resident Data;
Drop table Data;
store * from Temp into [C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd](qvd);
drop table Temp;
Exit Script;

3)Load the calculative dimension QVD created above and create a temp table to create date:-

New_Data:
CrossTable(Data, Values)
LOAD Customer,
OfficialCustomerPPM_Apr19,
OfficialCustomerPPM_May19,
OfficialCustomerPPM_Jun19,
OfficialCustomerPPM_Jul19,
OfficialCustomerPPM_Aug19,
OfficialCustomerPPM_Sep19,
OfficialCustomerPPM_Oct19,
OfficialCustomerPPM_Nov19,
OfficialCustomerPPM_Dec19,
OfficialCustomerPPM_Jan20,
OfficialCustomerPPM_Feb20,
OfficialCustomerPPM_Mar20,
SitePPMPerformance_Apr19,
SitePPMPerformance_May19,
SitePPMPerformance_Jun19,
SitePPMPerformance_Jul19,
SitePPMPerformance_Aug19,
SitePPMPerformance_Sep19,
Site_PPM_Performance_Oct19,
SitePPMPerformance_Nov19,
SitePPMPerformance_Dec19,
SitePPMPerformance_Jan20,
SitePPMPerformance_Feb20,
SitePPMPerformance_Mar20,
OfficialCustomerPB_Apr19,
OfficialCustomerIPB_May19,
OfficialCustomerIPB_Jun19,
OfficialCustomerIPB_Jul19,
OfficialCustomerIPB_Aug19,
Official_Customer_IPB_Sep19,
OfficialCustomerIPB_Oct19,
OfficialCustomerIPB_Nov19,
OfficialCustomerIPB_Dec19,
OfficialCustomerIPB_Jan20,
OfficialCustomerIPB_Feb20,
OfficialCustomerIPB_Mar20,
SitePerformanceIPB_Apr19,
SitePerformanceIPB_May19,
SitePerformanceIPB_Jun19,
SitePerformanceIPB_Jul19,
SitePerformanceIPB_Aug19,
SitePerformanceIPB_Sep19,
SitePerformanceIPB_Oct19,
SitePerformanceIPB_Nov19,
SitePerformanceIPB_Dec19,
SitePerformanceIPB_Jan20,
SitePerformanceIPB_Feb20,
SitePerformanceIPB_Mar20
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd]
(qvd) where RecNo()=25;

Temp:
Load
Customer,
Data,
SubField(Data,'_',1) as Dimension,
SubField(Data, '_',-1) as Date,
num(Date(Date#(SubField(Data, '_',-1),'MMMYY'),'DD/MM/YYYY') )As New_Date,
Values
Resident New_Data;
Drop Table New_Data;

4) Front End Visualization-

->Create a table ,pass this as dimension-> Date(New_Date, ‘MMM-YY’)

->Create 4 measures like this :-

                 *Official Customer IPB= Sum({<Dimension={'OfficialCustomerIPB'}>}Values)

       -*Official Customer PPM= Sum({<Dimension={'OfficialCustomerPPM'}>}Values)

       *Site Perfromance IPB= Sum({<Dimension={'SitePerformanceIPB'}>}Values)

       *Site Performance PPM= Sum({<Dimension={'SitePPMPerformance'}>}Values)

 

 

 

 

View solution in original post

4 Replies
jochem_zw
Partner Ambassador
Partner Ambassador

can you attach your data in XLSX format

Aspiring_Developer
Creator III
Creator III
Author

Hi,

Sure. I have attached the excel file.

 

Please consider the "Quality Perfo Dashboard-Yearly " tab for data. kindly ignore the rest of the tabs.

 

 

jochem_zw
Partner Ambassador
Partner Ambassador

sorry but the excel is bit complex, and not easy as a quick thing for me to do. it should be possible to do, but i think you have to make some manual changes to xls.

Aspiring_Developer
Creator III
Creator III
Author

Hi Team,

I have solved it my self.

I requested the user to make a slight change in the headers name and named it as "NOK parts in customer database - total_Apr19", "Number of official Written concern_May19", "Total number Alert + Unofficial Written concern_June19","Volumes shipped (external sales only)_July19", etc.

1) Simply load the data in QV:-

Data:
LOAD F1 as Customer,

[NOK parts in customer database - total_Apr19],
[Real NOK parts manufactured - total_Apr19],
[Number of official Written concern_ Apr19],
[Total number Alert + Written concern_Apr19],
[Volumes shipped (external sales only)_Apr19],

[NOK parts in customer database - total_May19],
[Real NOK parts manufactured - total_May19],
[Number of official Written concern_May19],
[Total number Alert + Written concern_May19],
[Volumes shipped (external sales only)_May19],

[NOK parts in customer database - total_Jun19],
[Real NOK parts manufactured - total_Jun19],
[Number of official Written concern_Jun19],
[Total number Alert + Written concern_Jun19],
[Volumes shipped (external sales only)_Jun19],

[NOK parts in customer database - total_Jul19],
[Real NOK parts manufactured - total_Jul19],
[Number of official Written concern_Jul19],
[Total number Alert + Written concern_Jul19],
[Volumes shipped (external sales only)_Jul19],

[NOK parts in customer database - total_Aug19],
[Real NOK parts manufactured - total_Aug19],
[Number of official Written concern_Aug19],
[Total number Alert + Written concern_Aug19],
[Volumes shipped (external sales only)_Aug19],

[NOK parts in customer database - total_Sep19],
[Real NOK parts manufactured - total_Sep19],
[Number of official Written concern_Sep19],
[Total number Alert + Written concern_Sep19],
[Volumes shipped (external sales only)_Sep19],

[NOK parts in customer database - total_Oct19],
[Real NOK parts manufactured - total_Oct19],
[Number of official Written concern_Oct19],
[Total number Alert + Written concern_Oct19],
[Volumes shipped (external sales only)_Oct19],

[NOK parts in customer database - total_Nov19],
[Real NOK parts manufactured - total_Nov19],
[Number of official Written concern_Nov19],
[Total number Alert + Written concern_Nov19],
[Volumes shipped (external sales only)_Nov19],

[NOK parts in customer database - total_Dec19],
[Real NOK parts manufactured - total_Dec19],
[Number of official Written concern_Dec19],
[Total number Alert + Written concern_Dec19],
[Volumes shipped (external sales only)_Dec19],

[NOK parts in customer database - total_Jan20],
[Real NOK parts manufactured - total_Jan20],
[Number of official Written concern_Jan20],
[Total number Alert + Written concern_Jan20],
[Volumes shipped (external sales only)_Jan20],

[NOK parts in customer database - total_Feb20],
[Real NOK parts manufactured - total_Feb20],
[Number of official Written concern_Feb20],
[Total number Alert + Written concern_Feb20],
[Volumes shipped (external sales only)_Feb20],

[NOK parts in customer database - total_Mar20],
[Real NOK parts manufactured - total_Mar20],
[Number of official Written concern_Mar20],
[Total number Alert + Written concern_Mar20],
[Volumes shipped (external sales only)_Mar20]
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report\OneDrive_2020-02-11\MOR and Quality Improvement Plan, FY2020\1. Global File\ZZ Global January 2019.xlsx]
(ooxml, embedded labels, header is 3 lines, table is [To be deleted -Vaishali])
where RowNo()<25;

 

2) Create Calculative Dimensions and store it into QVD:-

Temp:
Load
Customer,
//official Customer PPM

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

([NOK parts in customer database - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as OfficialCustomerPPM_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([NOK parts in customer database - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as OfficialCustomerPPM_May19,


if([Volumes shipped (external sales only)_Jun19]=0,'0',

( [NOK parts in customer database - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as OfficialCustomerPPM_Jun19,


if([Volumes shipped (external sales only)_Jul19]=0,'0',

( [NOK parts in customer database - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as OfficialCustomerPPM_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

( [NOK parts in customer database - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as OfficialCustomerPPM_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

( [NOK parts in customer database - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as OfficialCustomerPPM_Sep19,


if([Volumes shipped (external sales only)_Oct19]=0,'0',

( [NOK parts in customer database - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as OfficialCustomerPPM_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

( [NOK parts in customer database - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as OfficialCustomerPPM_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

( [NOK parts in customer database - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as OfficialCustomerPPM_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

( [NOK parts in customer database - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as OfficialCustomerPPM_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

( [NOK parts in customer database - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as OfficialCustomerPPM_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

( [NOK parts in customer database - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as OfficialCustomerPPM_Mar20,

 

//Site PPM Performance
if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Real NOK parts manufactured - total_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000
) as SitePPMPerformance_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Real NOK parts manufactured - total_May19]/[Volumes shipped (external sales only)_May19])*1000000
) as SitePPMPerformance_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Real NOK parts manufactured - total_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000
) as SitePPMPerformance_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Real NOK parts manufactured - total_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000
) as SitePPMPerformance_Jul19,

if([Volumes shipped (external sales only)_Aug19]=0,'0',

([Real NOK parts manufactured - total_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000
) as SitePPMPerformance_Aug19,

if([Volumes shipped (external sales only)_Sep19]=0,'0',

([Real NOK parts manufactured - total_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000
) as SitePPMPerformance_Sep19,

if([Volumes shipped (external sales only)_Oct19]=0,'0',

([Real NOK parts manufactured - total_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000
) as Site_PPM_Performance_Oct19,

if([Volumes shipped (external sales only)_Nov19]=0,'0',

([Real NOK parts manufactured - total_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000
) as SitePPMPerformance_Nov19,

if([Volumes shipped (external sales only)_Dec19]=0,'0',

([Real NOK parts manufactured - total_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000
) as SitePPMPerformance_Dec19,

if([Volumes shipped (external sales only)_Jan20]=0,'0',

([Real NOK parts manufactured - total_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000
) as SitePPMPerformance_Jan20,

if([Volumes shipped (external sales only)_Feb20]=0,'0',

([Real NOK parts manufactured - total_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000
) as SitePPMPerformance_Feb20,

if([Volumes shipped (external sales only)_Mar20]=0,'0',

([Real NOK parts manufactured - total_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000
) as SitePPMPerformance_Mar20,

 


//Official Customer IPB

if([Volumes shipped (external sales only)_Apr19]=0,'0',

([Number of official Written concern_ Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as OfficialCustomerPB_Apr19,

if([Volumes shipped (external sales only)_May19]=0,'0',

([Number of official Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as OfficialCustomerIPB_May19,

if([Volumes shipped (external sales only)_Jun19]=0,'0',

([Number of official Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as OfficialCustomerIPB_Jun19,

if([Volumes shipped (external sales only)_Jul19]=0,'0',

([Number of official Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as OfficialCustomerIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Number of official Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as OfficialCustomerIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Number of official Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as Official_Customer_IPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Number of official Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as OfficialCustomerIPB_Oct19,

if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Number of official Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as OfficialCustomerIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Number of official Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as OfficialCustomerIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Number of official Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as OfficialCustomerIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Number of official Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as OfficialCustomerIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Number of official Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as OfficialCustomerIPB_Mar20

 

,
//Site Performace IPB

if( [Volumes shipped (external sales only)_Apr19]=0,'0',

( [Total number Alert + Written concern_Apr19]/[Volumes shipped (external sales only)_Apr19])*1000000000
) as SitePerformanceIPB_Apr19,

if( [Volumes shipped (external sales only)_May19]=0,'0',

( [Total number Alert + Written concern_May19]/[Volumes shipped (external sales only)_May19])*1000000000
) as SitePerformanceIPB_May19,


if( [Volumes shipped (external sales only)_Jun19]=0,'0',

( [Total number Alert + Written concern_Jun19]/[Volumes shipped (external sales only)_Jun19])*1000000000
) as SitePerformanceIPB_Jun19,


if( [Volumes shipped (external sales only)_Jul19]=0,'0',

( [Total number Alert + Written concern_Jul19]/[Volumes shipped (external sales only)_Jul19])*1000000000
) as SitePerformanceIPB_Jul19,

if( [Volumes shipped (external sales only)_Aug19]=0,'0',

( [Total number Alert + Written concern_Aug19]/[Volumes shipped (external sales only)_Aug19])*1000000000
) as SitePerformanceIPB_Aug19,

if( [Volumes shipped (external sales only)_Sep19]=0,'0',

( [Total number Alert + Written concern_Sep19]/[Volumes shipped (external sales only)_Sep19])*1000000000
) as SitePerformanceIPB_Sep19,

if( [Volumes shipped (external sales only)_Oct19]=0,'0',

( [Total number Alert + Written concern_Oct19]/[Volumes shipped (external sales only)_Oct19])*1000000000
) as SitePerformanceIPB_Oct19,


if( [Volumes shipped (external sales only)_Nov19]=0,'0',

( [Total number Alert + Written concern_Nov19]/[Volumes shipped (external sales only)_Nov19])*1000000000
) as SitePerformanceIPB_Nov19,

if( [Volumes shipped (external sales only)_Dec19]=0,'0',

( [Total number Alert + Written concern_Dec19]/[Volumes shipped (external sales only)_Dec19])*1000000000
) as SitePerformanceIPB_Dec19,

if( [Volumes shipped (external sales only)_Jan20]=0,'0',

( [Total number Alert + Written concern_Jan20]/[Volumes shipped (external sales only)_Jan20])*1000000000
) as SitePerformanceIPB_Jan20,

if( [Volumes shipped (external sales only)_Feb20]=0,'0',

( [Total number Alert + Written concern_Feb20]/[Volumes shipped (external sales only)_Feb20])*1000000000
) as SitePerformanceIPB_Feb20,

if( [Volumes shipped (external sales only)_Mar20]=0,'0',

( [Total number Alert + Written concern_Mar20]/[Volumes shipped (external sales only)_Mar20])*1000000000
) as SitePerformanceIPB_Mar20

Resident Data;
Drop table Data;
store * from Temp into [C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd](qvd);
drop table Temp;
Exit Script;

3)Load the calculative dimension QVD created above and create a temp table to create date:-

New_Data:
CrossTable(Data, Values)
LOAD Customer,
OfficialCustomerPPM_Apr19,
OfficialCustomerPPM_May19,
OfficialCustomerPPM_Jun19,
OfficialCustomerPPM_Jul19,
OfficialCustomerPPM_Aug19,
OfficialCustomerPPM_Sep19,
OfficialCustomerPPM_Oct19,
OfficialCustomerPPM_Nov19,
OfficialCustomerPPM_Dec19,
OfficialCustomerPPM_Jan20,
OfficialCustomerPPM_Feb20,
OfficialCustomerPPM_Mar20,
SitePPMPerformance_Apr19,
SitePPMPerformance_May19,
SitePPMPerformance_Jun19,
SitePPMPerformance_Jul19,
SitePPMPerformance_Aug19,
SitePPMPerformance_Sep19,
Site_PPM_Performance_Oct19,
SitePPMPerformance_Nov19,
SitePPMPerformance_Dec19,
SitePPMPerformance_Jan20,
SitePPMPerformance_Feb20,
SitePPMPerformance_Mar20,
OfficialCustomerPB_Apr19,
OfficialCustomerIPB_May19,
OfficialCustomerIPB_Jun19,
OfficialCustomerIPB_Jul19,
OfficialCustomerIPB_Aug19,
Official_Customer_IPB_Sep19,
OfficialCustomerIPB_Oct19,
OfficialCustomerIPB_Nov19,
OfficialCustomerIPB_Dec19,
OfficialCustomerIPB_Jan20,
OfficialCustomerIPB_Feb20,
OfficialCustomerIPB_Mar20,
SitePerformanceIPB_Apr19,
SitePerformanceIPB_May19,
SitePerformanceIPB_Jun19,
SitePerformanceIPB_Jul19,
SitePerformanceIPB_Aug19,
SitePerformanceIPB_Sep19,
SitePerformanceIPB_Oct19,
SitePerformanceIPB_Nov19,
SitePerformanceIPB_Dec19,
SitePerformanceIPB_Jan20,
SitePerformanceIPB_Feb20,
SitePerformanceIPB_Mar20
FROM
[C:\Users\Vaishali\Documents\Work Varrooc\Dhiloj Qlik Report.qvd]
(qvd) where RecNo()=25;

Temp:
Load
Customer,
Data,
SubField(Data,'_',1) as Dimension,
SubField(Data, '_',-1) as Date,
num(Date(Date#(SubField(Data, '_',-1),'MMMYY'),'DD/MM/YYYY') )As New_Date,
Values
Resident New_Data;
Drop Table New_Data;

4) Front End Visualization-

->Create a table ,pass this as dimension-> Date(New_Date, ‘MMM-YY’)

->Create 4 measures like this :-

                 *Official Customer IPB= Sum({<Dimension={'OfficialCustomerIPB'}>}Values)

       -*Official Customer PPM= Sum({<Dimension={'OfficialCustomerPPM'}>}Values)

       *Site Perfromance IPB= Sum({<Dimension={'SitePerformanceIPB'}>}Values)

       *Site Performance PPM= Sum({<Dimension={'SitePPMPerformance'}>}Values)