script help

[bharatkishore]

HI All,

I have the follow code in script:

M:

load Distinct

EquipmentId,

[Part Group],

Key_EMS,

System_Code

Resident T1;

left join(M)

M1:

load

Key_EMS, Install_Date,

Removal_Date,

Material_Age,

if(Total_Quantity_Installed>=1,1,Total_Quantity_Installed) as Total_Quantity_Installed

Resident MAWhere Total_Quantity_Installed <> 0;

drop Table M;

DROP Table MA;

But the <> 0 is not working i am still getting 0 records.

Can you please help me where i am doing wrong.

Thanks,

Bharat

[olivierrobin]

hello

if you want to make a join, you don't need

M1:

only

left join(M)

where do you have  0 records ?

in M -> it means the load from T1 retrieves 0 record

[bharatkishore]

Thanks Oliver for the reply.

Please find the below image

In Total quantity column it is not showing 0 but in chart it is showing 0.. I am not sure what is going wrong.

Can you please help

[giovanneb]

Hi try this,

M:

load Distinct

EquipmentId,

[Part Group],

Key_EMS,

System_Code

Resident T1;

left join(M)

////////M1:

load

Key_EMS,

Install_Date,

Removal_Date,

Material_Age,

if(Total_Quantity_Installed>=1,1,Total_Quantity_Installed) as Total_Quantity_Installed

Resident MAWhere Total_Quantity_Installed <> 0;

///////drop Table M;

///////DROP Table MA;

[bharatkishore]

Sorry Not working.. let me paste you the code with right names:

Material_Master_final:

load Distinct

EquipmentId,

[Part Group],

Key_EMS,

System_Code

Resident Materials_Master_Raw;

left join(Material_Master_final)

load

Key_EMS, Install_Date,

Removal_Date,

Material_Age,

if(Total_Quantity_Installed>=1,1,Total_Quantity_Installed) as Total_Quantity_Installed

Resident MaterialAgeTable Where Total_Quantity_Installed <> 0;

drop Table Materials_Master_Raw;

DROP Table MaterialAgeTable;

Please check..

[olivierrobin]

in your listbox,  you have ONLY distinct values

in properties, show frequency and i think you will see that you have more than 1 row with the same value

so that the sum of all rows is 0

[bharatkishore]

PFA app..

Hope this helps and kindly let me know if i am missing something..

[olivierrobin]

As you can see , the values are greyed meaning the don't apply to your selection

if you try to add a table object, you will see that your column is empty

that means it is null for this key

[bharatkishore]

Yeah.. i need to remove those.. Can you please tell me how can i remove those..

[olivierrobin]

you can try to add a where clause in your load

where not(isnull(Total_Quantity_Installed))

it will work only if all your Key_EMS are in both table

In the contrary, add a new load after your join and add the clause at this point