topic Re: Validate the data using QV document in QlikView

Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 06:55:56 GMT

Hi,

I want to validate the data between the Hierarchy sheet and the mapping sheet in the attached excel.For example "Relaince Markets" which is there in the Level01 column in Hierarchy sheet exists in "Report Level" column as "0(Zero)"(Level01-01=Level 0 in Report level). "XYZ" which is t here in the Level02 column in Hierarchy sheet exists in "Report Level" column as "1"(Level02-1=Level 1 in Report level) and so on.

I want to validate this using qlikview document. As explained above if the level are not matching between the Hierarchy sheet and Mapping sheet. It should highlighted in RED background. So that user can understand where the issue lies.

Can some one please share the sample document which does the samething as above. I'm unable start with this requirement.

Data attached with this excel is just a sample data to understand my requirement.

Thanks in advance

Re: Validate the data using QV document

Tue, 15 Apr 2014 08:39:15 GMT

Hi,

can we know (from your mapping) parent - child relations? Is parent-child relation important in your analysis, or maybe you need only check levels?

regards

Darek

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 08:54:46 GMT

Thanks for you response.

As of now we will only need to check the levels. Paren-Child relationship is not required as of now. Please let me know if you have any more questions.

Re: Validate the data using QV document

Tue, 15 Apr 2014 08:59:09 GMT

Do you have idea how your QV report should look like?

I can imagine, that we can do 2 things during reload. We can transform hierarchy into mapping. We can also transform mapping into hierarchy. But the way deppends what you like to show your user in application

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 09:04:15 GMT

That is very good question. I appreciate that!

I never thought of the QV report as I was just thinking how I am going to finish this task You can suggest me whatever the way you want to show anything if fine for me later will try to build on that. As of now I just need the result.

See if you can help me on this in your convenient time.

Re: Validate the data using QV document

Tue, 15 Apr 2014 09:26:48 GMT

My biggest concern is the number of columns in your spreadsheet "Hierarchy".
Is it always 8 levels?

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 09:31:43 GMT

We have some many other columns after 8 levels but they are not levels. So we will compare only till level 08. Please let me know if you have any other questions!

Re: Validate the data using QV document

Tue, 15 Apr 2014 09:51:57 GMT

If you will have idea how your raport should look like, i will try to achieve that.

To complete your task i suggest you to try with crosstable function to transform your hierarchy worksheet into structure of you mapping worksheet.

Then you will have two tables like your "mapping" worksheet.

You may concatenate data from both, adding for example some descriptor, where is row from (hierarchy or mapping).

It should be easy then to create analyse to compare data from mapping with data from hierarchy.

I hope taht it helps

Regards

Darek

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 10:07:09 GMT

Thanks for your response!

Can you please high light only those rows in mapping sheet where is the level aren't matching. Hope it makes sense now. Please see if you can help me on this.

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 10:48:05 GMT

TEMP:
CrossTable(Data, Mapping)
LOAD View_Sort,

     Level01,

     Level02,

     Level03,

     Level04,

     Level05,

     Level06,

     Level07,

     Level08
FROM

[..\include\Hierarchy.xls]

(biff, embedded labels, header is 1 lines, table is [Hierarchy$]);

MAIN_TEMP:
LOAD Report_Level,

     Order,

     Mapping,

     Sample_Order
FROM

[..\include\Hierarchy.xls]

(biff, embedded labels, table is [Mapping$]);
left join(MAIN_TEMP)
load *
Resident TEMP

;

drop table TEMP;

MAIN:
load *,
if(isnull(Data),1,0) as flag
Resident MAIN_TEMP

;

drop table MAIN_TEMP;

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 10:49:37 GMT

You are asking me to do something like this?

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 11:34:21 GMT

Can someone please help me out on this? It is little urgent!

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 11:40:10 GMT

TEMP:
CrossTable(Report_Level, Mapping)
LOAD View_Sort,

     Level01,

     Level02,

     Level03,

     Level04,

     Level05,

     Level06,

     Level07,

     Level08
FROM

[..\include\Hierarchy.xls]

(biff, embedded labels, header is 1 lines, table is [Hierarchy$]);

TMP:
NoConcatenate
load num(right(Report_Level,Len(Report_Level)-5)) as Report_Level_1,

        Mapping,

        View_Sort
Resident TEMP

;

drop table TEMP;



MAIN_TEMP:
LOAD Report_Level,

     Order,

     Mapping,

     Sample_Order
FROM

[..\include\Hierarchy.xls]

(biff, embedded labels, table is [Mapping$]);
left join(MAIN_TEMP)
load *
Resident TMP

;

drop table TMP;

MAIN:
load *,
if(num(Report_Level)-num(Report_Level_1)=1,1,0) as flag
Resident MAIN_TEMP

;

drop table MAIN_TEMP;

Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 12:24:39 GMT

TMP:
NoConcatenate
load num(right(Report_Level,Len(Report_Level)-5)) as Report_Level_1,--Here it is
making numeric from the last digits..

Mapping,

View_Sort
Resident TEMP

;

drop table TEMP;

MAIN_TEMP:
LOAD Report_Level,

     Order,

     Mapping,

     Sample_Order
FROM

[..\include\Hierarchy.xls]

(biff, embedded labels, table is [Mapping$]);
left join(MAIN_TEMP)
load *
Resident TMP

;

drop table TMP;

MAIN:
load *,
if(num(Report_Level)-num(Report_Level_1)=1,1,0) as flag –Here it is
checking and updating the flag..
Resident MAIN_TEMP

;

drop table MAIN_TEMP;

Re: Re: Validate the data using QV document

qlikviewforum — Tue, 15 Apr 2014 12:49:42 GMT

Please find attachment