topic Re: Creating a new table and dropping the initial in QlikView

Creating a new table and dropping the initial

Wed, 25 Nov 2020 16:16:04 GMT

I am loading a table and deriving a new column during the load. Then I want to create a new table where the new column is 'Passed', and just load those records, have them in the new table and drop the first table. Is below correct? For some reason when I run this, I get all empty, and NewTable does not appear.

FirstTable:

Load [field1],

[field2],

[field3],

If (([field3]='Yes' and [field2]='No'), 'Passed', 'Not Passed') as NewField

FROM

MyFileLocation;

NewTable:

Load *

Resident FirstTable

Where NewField='Y';

Drop Table FirstTable;

Re: Creating a new table and dropping the initial

ciaran_mcgowan — Mon, 20 Jun 2016 16:29:53 GMT

Almost

Where NewField='Passed';

Re: Creating a new table and dropping the initial

krishnacbe — Mon, 20 Jun 2016 16:30:39 GMT

Hi,

Please find the corrected script. Hope that helps

FirstTable:

Load [field1],

[field2],

[field3],

If (([field3]='Yes' and [field2]='No'), 'Passed', 'Not Passed') as NewField

FROM

MyFileLocation;

NewTable:

Load *

Resident FirstTable

Where NewField='Passed';

Drop Table FirstTable;

Regards

Krishnapriya

Re: Creating a new table and dropping the initial

Mon, 20 Jun 2016 16:31:05 GMT

Sorry I copied incorrectly, I have it like that Where NewField='Yes'; but it does not work

Re: Creating a new table and dropping the initial

ciaran_mcgowan — Mon, 20 Jun 2016 16:31:48 GMT

Yeah, I misread the script. Corrected now.

Re: Creating a new table and dropping the initial

Mon, 20 Jun 2016 16:31:50 GMT

I have it like this but again I get empty.. not sure what the problem is....

Re: Creating a new table and dropping the initial

trdandamudi — Mon, 20 Jun 2016 16:34:00 GMT

Please share sample data, so that it will be easy for us to trouble shoot and get you an answer...

Re: Creating a new table and dropping the initial

krishnacbe — Mon, 20 Jun 2016 16:34:21 GMT

Can you share the data in excel or share the qvw.

Re: Creating a new table and dropping the initial

nagarjuna_kotha — Mon, 20 Jun 2016 17:15:56 GMT

Try this.............

Error Is in this :If ([field3]='Yes' and [field2]='No'), 'Passed', 'Not Passed') as NewField

FirstTable:

Load [field1],

[field2],

[field3],

If ([field3]='Yes' and [field2]='No', 'Passed', 'Not Passed') as NewField

FROM

MyFileLocation;

NewTable:

Load *

Resident FirstTable

Where NewField='Y';

Drop Table FirstTable;

-Nagarjun

Re: Creating a new table and dropping the initial

nagarjuna_kotha — Mon, 20 Jun 2016 17:19:06 GMT

you can use above script,

It will work.error in your script is there is one extra bracket after " [field2]='No' " this.

check and revert back

_nagarjun

Re: Creating a new table and dropping the initial

sunny_talwar — Mon, 20 Jun 2016 17:19:13 GMT

I think you missed the NoConcatenate...

FirstTable:

Load [field1],

[field2],

[field3],

If (([field3]='Yes' and [field2]='No'), 'Passed', 'Not Passed') as NewField

FROM

MyFileLocation;

NewTable:

NoConcatenate

Load *

Resident FirstTable

Where NewField='Y';

Drop Table FirstTable;

Without NoConcatenate, your NewTable won't be created because it has the same fields as FirstTable. So add that and you should be fine

Re: Creating a new table and dropping the initial

tamilarasu — Mon, 20 Jun 2016 17:27:41 GMT

Sunny,

Where clause is also wrong. It should be either 'Passed' or 'Not Passed'.

Where NewField='Y';

Where NewField='Passed';