topic Re: Loading dummy data in QlikView

Loading dummy data

bharatkishore — Thu, 28 Dec 2017 08:49:03 GMT

Hi All,

I have a qvd and loaded two fields, i.e.

load

country,

district,

'1' as kpi scope added a dummy field.

Now when i reload it i will get KPI scope as 1. Now i want to get 0 also in that field.

Is there only way is concatenation or any other way.. please let me know.

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 08:54:58 GMT

You can also do join

Table:

LOAD country,

district

FROM ...

Join (Table)

LOAD * INLINE [

kpi

];

Re: Loading dummy data

tresB — Thu, 28 Dec 2017 08:56:12 GMT

You can concatenate like:

load

country,

district,

'1' as kpi

From <>:

Load

null() as country,

null() as district,

0 as kpi

autogenerate 1;

But I am not sure what could be the case when you would want this. May be based on your business need the code might change accordingly.

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 08:58:33 GMT

Hi Sunny,

PFA file for more reference.

Please let me know if this is the correct approach or any other best way.

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 09:08:06 GMT

What you have should also work, but if you are looking for alternative, try this

Table:

LOAD Country_u,

Upper(Left([Country_u],2)) as Ctry,

[contract at consumption_u],

if([contract at consumption_u]='Contract Covered' ,'C', if([contract at consumption_u]='Contract Billed','C',if([contract at consumption_u]='T&M','T',if([contract at consumption_u]='Warranty','W')))) as Entitlement1

FROM

(qvd);

Left Join (Table)

LOAD * INLINE [

KPIScope

];

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 09:08:08 GMT

Hi Tresesco,

The reason why i needed KPI scope is as 0 and 1 is,

I have written the following if condition i.e.

Now i will be sending this data to a CSV file.

Then in that excel is we filter under Entitlement1 KPI scope should be 1 when it is for Contract Covered, and KPI scope should be 0 when it is for Contract Billed.

PFA app for more reference.

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 09:10:50 GMT

What about the other two options under [contract at consumption_u]? T&M AND Warranty?

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 09:13:08 GMT

Hi Sunny,

No need for T&M and Warranty.. Just needed only for Contract Covered and Contract Billed..

KPI scope should be 1 for Contract Covered and KPI scope should be 0 for Contract Billed

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 09:17:02 GMT

You can try this

Table:

LOAD Country_u,

Upper(Left([Country_u],2)) as Ctry,

[contract at consumption_u],

FROM

(qvd);

Left Join (Table)

LOAD * INLINE [

KPIScope, contract at consumption_u

1, Contract Covered

0, Contract Billed

];

Or you can use ApplyMap

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 12:03:55 GMT

Hi Sunny,

I have written this if condition

Now i need to create KPI scope where KPI scope should be 1 for Contract Covered, T&M and Warranty and KPI scope should be 0 only for Contract Billed.

Sorry for miscommunication earlier.

Please let me know if you need anything more.

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 12:09:23 GMT

Try this

Table:

LOAD Country_u,

Upper(Left([Country_u],2)) as Ctry,

[contract at consumption_u],

If([contract at consumption_u]='Contract Covered' ,'C', if([contract at consumption_u]='Contract Billed','C',if([contract at consumption_u]='T&M','T',if([contract at consumption_u]='Warranty','W')))) as Entitlement1,

If(Match([contract at consumption_u], 'Contract Covered', 'T&M', 'Warranty'), 1, 0) as KPIScope

FROM

(qvd);

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 12:22:38 GMT

Thank you genius.. Almost done.

While exporting it to CSV file i am getting some blanks in the final file.

Attached files for referrence. Can you please help me where i am missing.

Thanks,

Bharat

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 12:26:00 GMT

I don't see any nulls for the KPIScope

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 12:26:56 GMT

Sorry in the entitilement we have...

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 12:29:34 GMT

That is because you might have a 5th value (or may be more than 5 values) for [contract at consumption_u] field. Try this

If([contract at consumption_u] = 'Contract Covered', 'C',

If([contract at consumption_u] = 'Contract Billed', 'C',

If([contract at consumption_u] = 'T&M', 'T',

If([contract at consumption_u] = 'Warranty','W', 'N/A')))) as Entitlement1,

So, when none of the four conditions are met, you will see N/A

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 12:35:03 GMT

Thanks a lot genius.. Thank you so much......Thank u again...

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 12:43:29 GMT

Sunny,

Just one more question i have now in [contract at consumption_u] we have 6 fields,

Now i need to display only 4 fields data.

In the csv i don't want to show N/A or blanks. I want to show only C,T,M in entitlement

Can you please help me genius on this...

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 12:49:34 GMT

May be this

Table:

LOAD Country_u,

Upper(Left([Country_u],2)) as Ctry,

[contract at consumption_u],

If(Match([contract at consumption_u], 'Contract Covered', 'T&M', 'Warranty'), 1, 0) as KPIScope

FROM (qvd)

Where Match([contract at consumption_u], 'Contract Covered', 'Contract Billed', 'T&M', 'Warranty');

and I request you to stop addressing me as a 'genius' over and over again. I get that you are happy that I am helping you, but saying it over and over is not going to change that

Best,

Sunny

Re: Loading dummy data

bharatkishore — Thu, 28 Dec 2017 13:00:38 GMT

Thank you sunny.

Re: Loading dummy data

sunny_talwar — Thu, 28 Dec 2017 13:02:43 GMT

No problem my friend