Load Excel with merged row cell data and how to fix the data issues

[Anuhyak1]

Attached Excel Data with the merged row cells. I have two issues seen while loading the data in Qlik sense from Excel.

Issue 1:     Compliance Metrics FDA repeated twice in the excel one comes under GPSE E2B ICSR Submission Compliance %   and other FDA falls on DSUR Submission Compliance %

               Could you please help me with the script how to differentiate it and map the corresponding functional and threshold values  in the table.

EX:  If FDA falls under DSUR Submission Compliance % the functional areas would be PV Clin Ops and threshold will be <98% 

       IF  FDA falls under GPSE E2B ICSR Submission Compliance % the functional areas would be Ops and threshold will be <98% 

Expected Output:

 

Refer below screenshot after loading into Qlik sense and it is showing blank for Functional areas and threshold because FDA is repeated twice in the excel.

[justISO · Accepted solution]

Hi, to distinguish your FDA you simply can add RowNo() function and to populate missing data due to merged cells you can use peek() function. After you fill this, you can do CrossTable() function to transpose table and convert period columns to period values. Script to do all this could look like this:

temp:
LOAD
RowNo() as row,
    [Compliance Metrics],
    if(isnull([Functional Areas]), peek([Functional Areas]), [Functional Areas]) as [Functional Areas],
    if(isnull([Thres- hold]), peek([Thres- hold]), [Thres- hold]) as [Thres- hold],
    [44915],
    [44582],
    [44613],
    [44641],
    [44672],
    [44702],
    [44733],
    [44763],
    [44794],
    [44825],
    [44855],
    [44886],
    [44916]
FROM [lib://AttachedFiles/CrossTable conversion.xlsx]
(ooxml, embedded labels, table is [Compliance Dashbord Overview])
where not isnull([Compliance Metrics]);

main:
CrossTable(Period, [Compa Value], 4)
LOAD *
RESIDENT temp;

NoConcatenate
final:
LOAD
row, //if needed
[Compliance Metrics],
[Functional Areas],
replace([Thres- hold],'<','') as [Thres- hold], //or leave just [Thres- hold]
num([Compa Value]) as [Compa Value],
Date(num#(Period), 'YYYY-MM-DD') as [Month Year]
RESIDENT main;

DROP TABLES temp, main;

[Anuhyak1]

Thank you.

[Anuhyak1]

I have an issue.

We need to eliminate first row and first column from Qliksense Webfile also not miss the corresponding Functional Areas and Thresholds while loading data in Qlik sense. Please provide the script.

Attached Excel for your reference.

I have an excel below.

 I want to remove or Eliminate F1 column and row GPSE Compliance Dashboard row and load the rest of fields and values in Qlik sense.

When we write the below code it is missing the Functional Areas and Thersholds in the straight table.

NoConcatenate

temp:

LOAD

    [Compliance Metrics]  ,

    if(isnull([Functional Areas]), previous ([Functional Areas]), [Functional Areas]) as [Functional Areas],

   if(isnull([Thres- hold]), previous([Thres- hold]), [Thres- hold]) as [Thres- hold]

FROM [lib://$(vLib)]

(URL is [$(vUrl)$(v1)],ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);

JOIN 

LOAD

                                *

FROM [lib://$(vLib)]

(URL is [$(vUrl)$(v1)],ooxml, embedded labels, header is 1 lines, table is [$(sheetName)],

filters( rotate(left),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Compliance Metrics'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Functional Areas'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Thres- hold'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'F1'))),

rotate(right)

));

main:

CrossTable(Period, [Compa Value], 4)

LOAD *

RESIDENT temp;

No Concatenate

Compliance_Data_test:

LOAD

[Compliance Metrics],

[Functional Areas],

[Thres- hold],

//replace([Thres- hold],'<','') as [Thres- hold], 

num([Compa Value]) as [Compa Value],

Date(num#(Period), 'MMM-YYYY') as [Month Year]

RESIDENT main;

DROP TABLES temp, main;

Screenshot:

Attached Excel for your reference.

[Anuhyak1]

I have an excel below.

 I want to remove or Eliminate F1 column and row GPSE Compliance Dashboard row and load the rest of fields and values in Qlik sense.

When we write the below code it is missing the Functional Areas and Thersholds in the straight table.

NoConcatenate

temp:

LOAD

    [Compliance Metrics]  ,

    if(isnull([Functional Areas]), previous ([Functional Areas]), [Functional Areas]) as [Functional Areas],

   if(isnull([Thres- hold]), previous([Thres- hold]), [Thres- hold]) as [Thres- hold]

FROM [lib://$(vLib)]

(URL is [$(vUrl)$(v1)],ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);

JOIN 

LOAD

                                *

FROM [lib://$(vLib)]

(URL is [$(vUrl)$(v1)],ooxml, embedded labels, header is 1 lines, table is [$(sheetName)],

filters( rotate(left),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Compliance Metrics'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Functional Areas'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'Thres- hold'))),

Remove(Row,RowCnd(CellValue, 1, StrCnd(equal, 'F1'))),

rotate(right)

));

main:

CrossTable(Period, [Compa Value], 4)

LOAD *

RESIDENT temp;

No Concatenate

Compliance_Data_test:

LOAD

[Compliance Metrics],

[Functional Areas],

[Thres- hold],

//replace([Thres- hold],'<','') as [Thres- hold], 

num([Compa Value]) as [Compa Value],

Date(num#(Period), 'MMM-YYYY') as [Month Year]

RESIDENT main;

DROP TABLES temp, main;

Screenshot:

Attached Excel for your reference.