topic Data-comparision in QlikView

Data-comparision

Fri, 12 Nov 2010 11:00:07 GMT

Hi dear Community,

i have a comparison-data-load-problem. I must load monthly project-data in form of excel-files.Therefor i must compare the field "actual cost" with each file. so for example i have the month July and June. In June the actual cost was about 1000 $ and in July about 1500$ dollar. So when i add loading the new monthly excel-file for July, then the programm should replace the old fieldvalue of actual cost(from June) which was about 1000$ and should set an new fieldvalue with the new value of 1500$.

Can i anybody help me please by solving my problem?

Data-comparision

Fri, 12 Nov 2010 11:54:28 GMT

Hi ,

If I get you correct you are basically looking for insert + update function during loading .

You can check HELP to know more about it .

I pasted sample code below for your quick reference.

Insert and Update. (No Delete)

Script example:

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Regards,

Bhushan N

Data-comparision

Fri, 12 Nov 2010 12:39:00 GMT

Hi Bhushan,

first i want to thank you very much for answering and helping me. There is a little problem that i have by understanding your code cause my datas are only in form of excel and not like a database. so is it possible to match this code for using excel files. see, i have one excel file in june and then i want to update the existent data with the data files from july. so i have a monthly update and replace when i have different values for the actual cost field. i hope you can understand me:)))

best regards

dilan(but not like dylan) so i ´m a girl and not a boy;)

Data-comparision

Fri, 12 Nov 2010 13:31:58 GMT

Hi Dilan ,

It will work with your excel files too. Just need to do some coding .

If you can share sample data , i can work on sample code for your reference.

Regards,

Bhushan N

Data-comparision

Fri, 12 Nov 2010 13:58:13 GMT

Hello Again,

i would send you two excel_files if you want to give me your email adress, cause i try to paste a view of document here, but it is not really coherent:)

Data-comparision

malhaar — Fri, 12 Nov 2010 14:17:35 GMT

Another possible way to look at this problem:

Store the field as a Year-to-Date (YTD) value. At the time of presentation, use Set Analysis to subtract current month's YTD value with previous month's YTD value and arrive at the current month's actual value.

Data-comparision

Mon, 15 Nov 2010 06:21:42 GMT

Data-comparision

Mon, 15 Nov 2010 06:23:52 GMT

Hi ,

you can forward me your sample files to bnirgude @ gmail . com

I will work on them & send you QVW through this forum thread .

Cheers,

Bhushan N

Data-comparision

Mon, 15 Nov 2010 14:27:36 GMT

Hi bnirgude,

ok i will send you the files on your email. Thank you a lot for helping me!!!

Cheers,

Dilan

Data-comparision

Tue, 16 Nov 2010 06:39:04 GMT

Hi Dilan ,

Please find the attached application and i hope this will help you to understand how to implement .

I am sending you complete folder . I placed arbitatory "project_id".

Let me know if you have any doubts on the same .

Cheers,

Bhushan N

Data-comparision

Tue, 16 Nov 2010 14:24:51 GMT

Hello bhushan,

thank you very much for your support and for helping me. But i think i´m not allowed to open the file with the ending .qvw cause i use the personel edition and i think i get problems with the licence if i open your file ( as far as i know external files from other user are not allowed to use for a personel user like me). Perhaps you can explain me the rigth way to solve my problem or send me the code you have implement in form of .txt Thank you again and again for helping me.

Cheers,

Dilan

Data-comparision

Wed, 17 Nov 2010 05:46:57 GMT

Hi Dilan ,

No Probs ...

I am attaching the code IN word format .

Simply copy this code & put it in your QV Doc.

Regards,

Bhushan N

Data-comparision

Wed, 17 Nov 2010 09:35:59 GMT

oh bhushan,

you are a hero:) thank you very much..again and again...i will just try it with the code and wil tell you about the result!

thank you very very much!!!

Regards

Dilan

Data-comparision

Wed, 17 Nov 2010 09:54:14 GMT

Hi Dilan ,

You are always welcome

Regards,

Bhushan N

Data-comparision

Wed, 17 Nov 2010 12:11:37 GMT

Again hi to you bhushan,

first i want to tell you that your code is really perfect:) THANKS!!

and second is, do you now how to set indicators in qlikview, for watching field values and working as a kind of alarms like a signal light:)

for example: if a current field value is over 1000$ than drop an exception for warning the user or the field will lighten in RED or something like this. i just try it with the alarm function in qlikview but i haven´t done it very well i think cause it is not working perfectly...

i hope you understand me:))

Data-comparision

Wed, 17 Nov 2010 13:30:41 GMT

Hi Dilan ,

Check this one .

Regards,

Bhushan N

Data-comparision

Wed, 17 Nov 2010 14:05:32 GMT

oh yeah,

but i'm not allowed to open your .qvw file:(

perhaps you can tell me how you do it??

thank you..

Data-comparision

Wed, 17 Nov 2010 14:29:19 GMT

Hi Dilan ,

please check this

Bhushan N

Data-comparision

Wed, 17 Nov 2010 15:54:40 GMT

Hi bnirgude,

i think you sent the same file twice. they have the both ending with .qvw and i´m still not allowed to open them as a personel enduser:)

Data-comparision

Thu, 18 Nov 2010 06:14:17 GMT

Hi Dilan ,

Sorry i missed that you are having personal edition .

You can follow below steps.

Stpe-1) You can create an expression .

Step-2) Chart ---> Properties ---> Expressions --->Display Options --- > Representation --------------------- change it to "Image"

Stpe-3) & paste below mention code in Definition .

(

Sum ([Projectcost (actual)])<1000,'qmem://<bundled>/BuiltIn/smiley3_r.png','qmem://<bundled>/BuiltIn/smiley1_g.png')

Cheers ,

Bhushan N