Loading dummy data

[bharatkishore]

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

[sunny_talwar]

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);

[bharatkishore]

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

[sunny_talwar]

I don't see any nulls for the KPIScope

[bharatkishore]

Sorry in the entitilement we have...

[sunny_talwar · Accepted solution]

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

[bharatkishore]

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

[bharatkishore]

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...

[sunny_talwar]

May be 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)

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

[bharatkishore]

Thank you sunny.

[sunny_talwar]

No problem my friend