topic Re: script help in QlikView

script help

bharatkishore — Mon, 03 Sep 2018 14:44:10 GMT

HI All,

I have the follow code in script:

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

Re: script help

olivierrobin — Mon, 03 Sep 2018 14:47:25 GMT

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

Re: script help

bharatkishore — Mon, 03 Sep 2018 14:50:35 GMT

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

Re: script help

giovanneb — Mon, 03 Sep 2018 14:51:12 GMT

Hi try this,

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;

Re: script help

bharatkishore — Mon, 03 Sep 2018 14:55:19 GMT

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

Re: script help

olivierrobin — Mon, 03 Sep 2018 14:55:34 GMT

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

Re: script help

bharatkishore — Mon, 03 Sep 2018 14:59:40 GMT

PFA app..

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

Re: script help

olivierrobin — Mon, 03 Sep 2018 15:08:14 GMT

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

Re: script help

bharatkishore — Mon, 03 Sep 2018 15:09:48 GMT

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

Re: script help

olivierrobin — Mon, 03 Sep 2018 15:12:15 GMT

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

Re: script help

bharatkishore — Mon, 03 Sep 2018 15:20:47 GMT

I have added the below code:

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 ;

drop Table Materials_Master_Raw;

DROP Table MaterialAgeTable;

Final:

Load * Resident Material_Master_final

Where not(isnull(Total_Quantity_Installed)) ;

But this table is not loading at all.

Re: script help

olivierrobin — Mon, 03 Sep 2018 15:23:41 GMT

as it has the same structure as the preceeding table, they are concatenated

don't forget to use the noconcatenate prefix

Re: script help

bharatkishore — Mon, 03 Sep 2018 15:31:42 GMT

Thanks a lot Olivier for your help..

After using Noconcatenate i dropped the final table. Then i have >0 instead of not is null and then it is working..

Thank you so much..