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: 
qlikviewforum
Creator II
Creator II

Validate the data using QV document

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

14 Replies
Not applicable

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

qlikviewforum
Creator II
Creator II
Author

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.


Not applicable

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

qlikviewforum
Creator II
Creator II
Author

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.


Not applicable

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

qlikviewforum
Creator II
Creator II
Author

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!

Not applicable

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

qlikviewforum
Creator II
Creator II
Author

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.

qlikviewforum
Creator II
Creator II
Author

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;